it gives error like.... - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

it gives error like....

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-09-15 : 14:14:05

Hello,

As below my Stored Procedure,

ALTER PROCEDURE [dbo].[ERS_SP_UpdateAllCodes]
-- Add the parameters for the stored procedure here
@TableName nvarchar(100),
@FirstColumn nvarchar(50),
@SecondColumn nvarchar(50),
@FirstVal int,
@SecondVal nvarchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

DECLARE @ExecQuery nvarchar(100)
DECLARE @Firs nvarchar(100)
SET @ExecQuery =@TableName
-- Insert statements for procedure here
select @ExecQuery = 'UPDATE [' + @TableName + '] SET' + '[' + @FirstColumn +']=@FirstVal,['+ @SecondColumn +']=@SecondVal WHERE [' + @FirstColumn+']=@FirstVal'
exec sp_executeSQL @ExecQuery, N'@FirstVal int,@SecondVal nvarchar(100)', @FirstVal,@SecondVal
END

When I try to execute it

It gives me error like

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Firs".

with below script

DECLARE @return_value int

EXEC @return_value = [dbo].[ERS_SP_UpdateAllCodes]
@TableName = N'ERS_RankCodes',
@FirstColumn = N'RankCode',
@SecondColumn = N'RankDescription',
@FirstVal = 1,
@SecondVal = N'newbhura'

SELECT 'Return Value' = @return_value

i dont understand that I dont have any parameter like @Firs

please help me out,
Regards,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-15 : 14:28:59

Please do not create a stored procedure like this. This is a very bad idea.

Why do you need to do this dynamically?!

To answer your question, it's because you didn't define @ExecQuery be enough. Make it 1000 or similar as 100 isn't enough to store your string.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-15 : 14:38:31

Plus there's a few others

Well let's see, the maximum lengths of all your inputs is 400 + an integer, yet your @ExecQuery only has room for 100, so make that length much bigger.

There's no need to update FirstColumn to @FirstVal since your where statement says they're already =

And you have the " ' ",s in the wrong places



select @ExecQuery 
= 'UPDATE [' + @TableName + '] SET ' + '[' + @FirstColumn +']= ' + convert(varchar(10),@FirstVal)
 + ',['+ @SecondColumn +']= '''+@SecondVal +''''
 + ' WHERE [' + @FirstColumn+']= ' + convert(varchar(10),@FirstVal)
select @ExecQuery

Jim

Everyday I learn something that somebody else already knew

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-15 : 17:48:53

Oooh, a SQL injection nightmare. Hope this isn't an internet-facing website.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources