Must declare scalar variable??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Must declare scalar variable???

Author

Topic

magnetica
Starting Member

36 Posts

Posted - 2006-08-31 : 06:31:11

OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:-
ERROR
"Must declare scalar variable @iuser"

Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!

//BEGIN

ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))

AS
Declare @sql nvarchar(1000)

SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (@iuser)'

EXECUTE (@sql)

RETURN
//END

And i know my VB.NET code is working but i will put it in anyway:-

//BEGIN

'variables
Dim user As String
user = Profile.UserName
Dim intenduser As String
intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()

'connection settings
Dim cs As String
cs = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)
'parameters
Dim cmd As New SqlCommand("putpending", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@cuuser").Value = user
cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@iuser").Value = intenduser
'execute
scn.Open()

cmd.ExecuteNonQuery()

scn.Close()
//END

Any ideas why i am getting this error message?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 06:51:26

What is pending keyword?

Also rewrite your dynamic sql as

SELECT @sql = 
'INSERT INTO ' +
@cuuser + 
' (Pending) VALUES (' + @iuser + ')'

EXECUTE (@sql)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 06:52:51

[code]ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))

AS
Declare @sql nvarchar(1000)

SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (''' + @iuser + ''')'

EXECUTE (@sql)

RETURN[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 06:55:20

Finally

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 07:01:15

quote:
Originally posted by Peso

What is pending keyword?

Peso,
I believe Pending is the column name, not a keyword !!

Harsh Athalye
India.
"Nothing is Impossible"

magnetica
Starting Member

36 Posts

Posted - 2006-08-31 : 07:01:31

Perfect!!
Cheers people, always something silly isnt it! lol
Oh and Pending is simply a column name within my table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 07:05:51

Ah! Now I see it too.
All this dynamic SQL makes me dizzy

Thanks, Harsh.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-31 : 07:55:14

quote:
Originally posted by Peso
All this dynamic SQL makes me dizzy

me too!

Harsh Athalye
India.
"Nothing is Impossible"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-31 : 08:36:32

Why is the table to insert into a parameter ?

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 08:42:59

Because normalization is not heard of?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-31 : 12:05:07

quote:
Originally posted by Peso

Finally

Peter Larsson
Helsingborg, Sweden

Yes Finally you got it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources