Dy

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.):
[Code]
DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject varchar(10
)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sAgentid ='0'
SELECT @.sProject = 'A'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
IF @.sAgentId <> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
END
IF @.sProject<> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
END
EXEC (@.SQL)
If I put the @.RC inside the quotes I get error: Must declare the variable
'@.RC'.
I tested by printing the statement and it prints properly just errors when
executing.
How can I make this work?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200507/1EXEC() has its own scope, so has no idea what @.RC is (nor does it "see"
sAgentID or @.sProject).
Try getting rid of the dynamic SQL altogether:
SELECT @.RC = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND COALESCE(sRawLogout, 'x') = 'x'
AND sAgentID = CASE @.sAgentID WHEN 'O' THEN sAgentID ELSE @.sAgentID
END
AND sProject = CASE @.sProject WHEN 'O' THEN sProject ELSE @.sProject
END
Or see http://www.aspfaq.com/2492
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:51A8B62C943CC@.webservertalk.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.):
> [Code]
> DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject
> varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sAgentid ='0'
> SELECT @.sProject = 'A'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> IF @.sAgentId <> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
> END
> IF @.sProject<> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
> END
> EXEC (@.SQL)
> If I put the @.RC inside the quotes I get error: Must declare the variable
> '@.RC'.
> I tested by printing the statement and it prints properly just errors when
> executing.
> How can I make this work?
> Thanks,
> Ninel
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200507/1|||I have a similar problem.
USE MyDataBase
GO
if exists
(select * from sysobjects where id =
object_id(N'[dbo].[sp_MTV_Diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MTV_Diskalert]
GO
create procedure sp_MTV_diskalert (
@.LowerLimit INT
, @.LinkedServerName SYSNAME
, @.NumberOfEmailsToSend INT
, @.EmailAddress1 NVARCHAR(30)
, @.EmailAddress2 NVARCHAR(30))
AS
--
BEGIN
--
SET XACT_ABORT ON
SET NOCOUNT ON
--
DECLARE @.RCPT VARCHAR(500)
, @.LIMIT INT
, @.SQLSTRING NVARCHAR(4000)
, @.STRING2 NVARCHAR(4000)
, @.EMAIL NVARCHAR(4000)
, @.ParmDefinition NVARCHAR(4000)
, @.ParmIn NVARCHAR(4000)
, @.ParmRet NVARCHAR(4000)
, @.MyCounter int
, @.TempEmailAddress NVARCHAR(30)
--
set @.MyCounter = 0
--
--The following stmt works
--
exec (select @.EmailAddress1 as name)
--
while ( @.MyCounter <> @.NumberOfEmailsToSend )
Begin
SET @.MyCounter = @.MyCounter + 1
SET @.TempEmailAddress = '@.EmailAddress'+ltrim(str(@.MyCounter))
SET @.SQLString = N'Select ' +@.TempEmailAddress+ ' as name'
-- print @.SQLstring
EXECUTE sp_executesql
@.SQLString
,@.params = N'@.email nvarchar(30) output'
,@.email=@.email output
END
END
go
EXEC Sealedair.dbo.sp_SAC_diskalert
250,DUNSQL02,1,'Michael.varriale@.sealedair.com','2','3','4','5','6','7','8',
'9','10'
Must declare the variable '@.EmailAddress1'.