Dynamic SQL Question

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,
Ninelhi 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(*)
> 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
>|||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|||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.
"ninel" <ngorbunov@.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
>|||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|||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
"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
>