Dynamic SQL Question

22 Jul 2005 5:58 AM

ninel

I have to get a count of records using dynamic sql. I have the following and I'm getting errors (Syntax error converting the varchar value 'SELECT ' to a column of data type int.): DECLARE @sCalldate varchar(10) DECLARE @SQL varchar(8000) DECLARE @RC int SELECT @sCalldate ='20050712' SELECT @sEmployeeNameId = '0' SELECT @sAgentid ='0' SELECT @SQL = 'SELECT ' + @RC + ' = Count(*) FROM dbo.tmPunchTime WHERE scalldate = ' + @sCallDate + ' AND ISNULL(sRawLogout, ''x'') = ''x''' EXEC (@SQL) How can I make this work? Thanks, Ninel

22 Jul 2005 6:14 AM

Enric

hi ninel,

Please, post DDL instructions but at first, try to sustitute this line for
the another one:

> SELECT @SQL =     'SELECT ' + @RC + ' = Count(*)
>         FROM dbo.tmPunchTime
>         WHERE scalldate = ' + @sCallDate +
>         ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@SQL)

>     set @sql = ('

SELECT ' + @RC + ' = Count(*)
Show quote

>         FROM dbo.tmPunchTime
>         WHERE scalldate = ' + @sCallDate +
>         ' AND ISNULL(sRawLogout, ''x'') = ''x'''')

> EXEC (@SQL)

"ninel" wrote:

> I have to get a count of records using dynamic sql.
>
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
>
> DECLARE @sCalldate varchar(10)
> DECLARE @SQL varchar(8000)
> DECLARE @RC int
>
> SELECT @sCalldate ='20050712'
> SELECT @sEmployeeNameId = '0'
> SELECT @sAgentid ='0'
>
> SELECT @SQL =     'SELECT ' + @RC + ' = Count(*)
>         FROM dbo.tmPunchTime
>         WHERE scalldate = ' + @sCallDate +
>         ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@SQL)
>
> How can I make this work?
>
> Thanks,
> Ninel
>
>

22 Jul 2005 6:20 AM

Razvan Socol

I see no need for dynamic SQL here:

DECLARE @sCalldate varchar(10)
DECLARE @RC int

SELECT @sCalldate ='20050712'

SELECT @RC=Count(*)
        FROM dbo.tmPunchTime
        WHERE scalldate = @sCallDate
        AND ISNULL(sRawLogout, 'x') = 'x'

Razvan

22 Jul 2005 7:20 AM

Micle

Hi!

declare
    @sql varchar(8000),
    @sCalidate varchar(10),
    @RC int;

set @sql = 'select @RC = count(*) from from tmPunchTime where scalldate =
@sCallDate and is null(sRawLogout, ''x'') = ''x''';

exec sp_executesql @sql, N'@RC int out, @sCalidate varchar(10)', @RS out,
@sCalidate = @sCalidate;

select @RC

Micle.

Show quote

"ninel" <ngorbu***@onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:orOdnbUkTPNtF33fRVn_vA@giganews.com...
>I have to get a count of records using dynamic sql.
>
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
>
> DECLARE @sCalldate varchar(10)
> DECLARE @SQL varchar(8000)
> DECLARE @RC int
>
> SELECT @sCalldate ='20050712'
> SELECT @sEmployeeNameId = '0'
> SELECT @sAgentid ='0'
>
> SELECT @SQL =     'SELECT ' + @RC + ' = Count(*)
>        FROM dbo.tmPunchTime
>        WHERE scalldate = ' + @sCallDate +
>        ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@SQL)
>
> How can I make this work?
>
> Thanks,
> Ninel
>

22 Jul 2005 9:48 AM

jsfromynr

Hi Ninel,
I agree with Razvan Socol that there is no need for Dynamic SQL.
Please visit this link and view When not to use Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html ( An authority on Dynamic
SQL)

If in your case you donot know TableName or Parameters are changing
then Micle approach is what you need
Please let me know if it clear your doubts.

With warm regards
Jatinder

22 Jul 2005 2:12 PM

JosephPruiett

No need to use dynmaic sql to get the results you want. Please see example
below.

DECLARE @sCalldate varchar(10)
DECLARE @SQL varchar(8000)
DECLARE @RC int
DECLARE @sEmployeeNameId int
DECLARE @sAgentid int
SELECT @sCalldate ='20050712'
SELECT @sEmployeeNameId = '0'
SELECT @sAgentid ='0'

CREATE TABLE #tmPunchTime
(
Scalldate datetime,
sRawLogout sysname
)

Insert #tmPunchTime
Values ('20050712','x')

Insert #tmPunchTime
Values ('20050712','x')

Insert #tmPunchTime
Values ('20050712','x')

SELECT @RC =(SELECT Count(*)
        FROM #tmPunchTime
        WHERE convert(nvarchar,scalldate,112) = @sCallDate
         AND ISNULL(sRawLogout, 'x') = 'x')
SELECT @RC

DROP TABLE #tmPunchTime

Show quote

"ninel" wrote:

> I have to get a count of records using dynamic sql.
>
> I have the following and I'm getting errors (Syntax error converting
> the varchar value 'SELECT ' to a column of data type int.):
>
> DECLARE @sCalldate varchar(10)
> DECLARE @SQL varchar(8000)
> DECLARE @RC int
>
> SELECT @sCalldate ='20050712'
> SELECT @sEmployeeNameId = '0'
> SELECT @sAgentid ='0'
>
> SELECT @SQL =     'SELECT ' + @RC + ' = Count(*)
>         FROM dbo.tmPunchTime
>         WHERE scalldate = ' + @sCallDate +
>         ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> EXEC (@SQL)
>
> How can I make this work?
>
> Thanks,
> Ninel
>
>