Must declare the scalar variable "@Contents".

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable "@Contents".

Author

Topic

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-16 : 09:34:57

my sp
CREATE PROCEDURE [dbo].[sp_UpdateContents]
@strDB varchar(MAX),
@IDKEY bigint,
@Contents varchar(MAX),
@Tags varchar(MAX),
@Title varchar(MAX)
AS
BEGIN
DECLARE @strSQL nvarchar(4000)
SET @strSQL = 'UPDATE [@strDB].[dbo].[contents]

SET

contents=@Contents,
tags=@Tags,
title=@Title

WHERE idkey=@IDKEY'
EXEC sp_executesql @strSQL
END

the query:
sp_UpdateContents '[ghaapjghinternetcoza]',[1],'[testtestasdsadsadsadasdasdasdasd]','[tagtagsthe]','[title]'

the error:
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@Contents".

i'm stumped, i had got past this error earlier and now its back
what am i doing wrong here?

Thanks
Shem

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-16 : 09:40:11

nevermind, i'm stupid...

Thanks
Shem

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 09:52:43

So you have resolve it ?
You need to declare the parameters in the sp_executesql

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-16 : 09:58:46

changed my sp to:
CREATE PROCEDURE [dbo].[sp_UpdateContents]
@strDB varchar(MAX),
@IDKEY bigint,
@Contents varchar(MAX),
@Tags varchar(MAX),
@Title varchar(MAX)
AS
BEGIN
DECLARE @strSQL nvarchar(4000)
SET @strSQL = 'UPDATE [' + @strDB + '].[dbo].[contents]

SET

contents=' + @Contents + ',
tags=' + @Tags + ',
title=' + @Title + '

WHERE idkey=' + @IDKEY + ''
EXEC sp_executesql @strSQL
END

but now i'm getting:
Msg 8114, Level 16, State 5, Procedure sp_UpdateContents, Line 10
Error converting data type varchar to bigint.

any idea why this is happening, the only bigint is @IDKEY, how can it be assigning it to Line 10 which is:
SET @strSQL = 'UPDATE [' + @strDB + '].[dbo].[contents]

Thanks
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:04:18

[code]CREATE PROCEDURE dbo].sp_UpdateContents
(
@strDB VARCHAR(MAX),
@IDKEY BIGINT,
@Contents VARCHAR(MAX),
@Tags VARCHAR(MAX),
@Title VARCHAR(MAX)
)
AS

SET NOCOUNT ON

DECLARE @strSQL nvarchar(4000)

SET @strSQL = 'UPDATE ' + @strDB + '.[dbo].[contents]
SET contents = ''' + @Contents + ''',
tags = ''' + @Tags + '''
title = ''' + @Title + '''
WHERE idkey = ''' + CAST(@IDKEY AS VARCHAR) + ''''

EXEC (@strSQL)[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:05:21

Your code posted 03/16/2007 : 09:58:46 will not work.
The SQL Server will think @Content is a column, not a value.

See my post above.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-16 : 10:10:15

or the sp_executesql way


DECLARE @strSQL nvarchar(4000)

SET @strSQL = 'UPDATE [' + @strDB + '].[dbo].[contents]' + char(13)
SET @strSQL = @strSQL + 'SET contents = @Contents,'      + char(13)
SET @strSQL = @strSQL + '    tags     = @Tags,'          + char(13)
SET @strSQL = @strSQL + '    title    = @Title'          + char(13)
SET @strSQL = @strSQL + 'WHERE idkey  = @IDKEY'          + char(13)

-- Print out to verify
PRINT @strSQL

EXEC sp_executesql @strSQL, 
                 N'@Contents varchar(MAX), @Tags VARCHAR(MAX), @Title VARCHAR(MAX), @IDKEY BIGINT',
                   @Contents, @Tags, @Title, @IDKEY

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-16 : 10:13:14

Thanks for the fix Peso :)

exec sp_UpdateContents 'ghaapjghinternetcoza',1,'testtestasdsadsadsadasdasdasdasd','tagtagsthe','title'

brings up

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'title'.

am i missing something here, title being the last value, it looks the same as the others?

Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:24:42

Remove 4 ' ('' '') around @strDB

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-16 : 10:32:00

i'm not sure I understand?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 10:34:18

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-19 : 03:02:01

this is the print out from khtan

UPDATE [ghaapjghinternetcoza].[dbo].[contents]
SET contents = @Contents,
tags = @Tags,
title = @Title
WHERE idkey = @IDKEY

and it's working now, thanks a stack;)
Shem

mjmj75
Starting Member

5 Posts

Posted - 2007-03-28 : 12:13:28

I am trying to get reports to run in SMS which is using SQL in the background. I copied a statement that runs correctly from one server which is listed below. When I posted this in another server, I received the error "Must declare the scalar variable "@Bulletin ID"
@CollID"

I don't understand why it won't work, I just copied the text from one server to the other.

Please HELP!

declare @Total int, /* total count collection membership */
@SMSInstall int, /* count installed by SMS */
@OtherInstall int, /* count installed externally */
@Missing int, /* count missing patch */
@NotRequired int, /* count not requiring patch */
@Required int, /* count requiring patch */
@Outstanding int /* count outstanding */

/* count non-obsolete clients */
select @Total=count(*)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
where IsNull(sys.Obsolete0,0)=0 and sys.Client0=1 and fcm.CollectionID=@CollID

/* patches installed by SMS */
/* patches installed by others */
/* patches required by systems */
/* v_GS_PatchStatusEx already filters out obsolete clients */
select @SMSInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is not null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is not null and ps2.LastState=105 then ps2.ResourceID
else null end),
@OtherInstall=count(distinct case
when ps1.LastState is not null and ps1.AgentInstallDate is null and ps1.LastState=105 then ps1.ResourceID
when ps1.LastState is null and ps2.AgentInstallDate is null and ps2.LastState=105 then ps2.ResourceID
else null end),
@Missing=count(distinct case
when ps1.LastState is not null and ps1.LastState!=105 then ps1.ResourceID
when ps1.LastState is null and ps2.LastState is not null and ps2.LastState!=105 then ps2.ResourceID
else null end),
@Required=count(distinct case
when ps1.ResourceID is null then ps2.ResourceID else ps1.ResourceID end)
from (select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber and
UniqueUpdateID is not null) ps1
full outer join
(select LastState, AgentInstallDate, ResourceID, UpdateID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber and
UniqueUpdateID is null) ps2
on ps1.ResourceID=ps2.ResourceID
join v_FullCollectionMembership fcm
on (ps2.ResourceID is null and ps1.ResourceID=fcm.ResourceID) or
(ps1.ResourceID is null and ps2.ResourceID=fcm.ResourceID) or
(ps1.ResourceID=fcm.ResourceID and ps2.ResourceID=fcm.ResourceID)
where fcm.CollectionID=@CollID

/* not requiring patch */
select @NotRequired=count(distinct fcm.ResourceID)
from v_FullCollectionMembership fcm
join v_R_System sys on fcm.ResourceID=sys.ResourceID
join v_GS_SCANPACKAGEVERSION spv on fcm.ResourceID=spv.ResourceID
join (select upkg.PackageID, max(upkg.PackageVersion) as PackageVersion
from v_ApplicableUpdatesSummaryEx us
join v_UpdatePrograms upkg on us.UpdateID=upkg.UpdateID
where us.ID=@BulletinID and us.QNumbers=@QNumber and upkg.PackageType=1
group by upkg.PackageID) updpkg
on spv.PackageID0=updpkg.PackageID and spv.PackageVer0>=updpkg.PackageVersion
left join (select ResourceID
from v_GS_PatchStatusEx
where ID=@BulletinID and QNumbers=@QNumber) ps
on fcm.ResourceID=ps.ResourceID
where fcm.CollectionID=@CollID and
ps.ResourceID is null and IsNull(sys.Obsolete0,0)=0 and sys.Client0=1

/* outstanding computers */
Select @Outstanding=@Total-(@NotRequired+@Required)

select @Total as 'Computers in collection'
select @Required as 'Computers requiring update', 100*@Required/@Total as '% of Total'
select @SMSInstall as 'Computers updated by SMS', 100*@SMSInstall/@Total as '% of Total'
select @OtherInstall as 'Computers updated by external means', 100*@OtherInstall/@Total as '% of Total'
select @SMSInstall+@OtherInstall as 'Total computers updated', 100*(@SMSInstall+@OtherInstall)/@Total as '% of Total'
select @Missing as 'Computers missing update', 100*@Missing/@Total as '% of Total'
select @NotRequired as 'Computers not requiring update', 100*@NotRequired/@Total as '% of Total'
select @Outstanding as 'Outstanding computers', 100*@Outstanding/@Total as '% of Total'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 12:21:47

where is the declaration of @BulletinID ? I don't see it in your code anywhere.
EDIT : OP posted as new thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81341

mjmj75
Starting Member

5 Posts

Posted - 2007-03-28 : 14:03:24

Thanks now the report are generating. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources