Trying to use variables in a view.

Question

Trying to use variables in a view.

itskumar2004

Default port

Points: 1446
More actions
April 26, 2010 at 9:23 am

#97276

Hi,
I want to use a variable in a view.so i created a stored procedure and in that i have created a view .but, when i used the variable ina view and try to execute the procedure.
i am getting the error :
Must declare the scalar variable "@a".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

the error tells what the issue is, but it might be a little new to you for debugging these kinds of issues.

to really help; we'd have to see the code for the stored procedure; anything else we did would just be syntax guessing, which might just confuse everyone even more.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aluminum Ten Centuries Points: 1132 More actions · Answer 2

Try using dynamic SQL to concatinate the variable into your view. I assume you do not want the variable to be created as part of the view rather whatever the variables value is to be created in the view.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

As Lowell said, providing the code will allow us to help you refactor it or tell you a better way to go... but barring that:

You can't use variables in a view. Instead of putting the 'create view' inside of a stored procedure, just use the variables in the stored procedure and do the select and don't create the view at all.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Tim Cullen Ten Centuries Points: 1374 More actions · Answer 4

I would try creating a table function instead of a view since you want to pass parameters; there are some limitations on what can be done in a function: http://msdn.microsoft.com/en-us/library/ms186755.aspx

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

itskumar2004 (4/26/2010)
Hi,
I want to use a variable in a view.so i created a stored procedure and in that i have created a view .but, when i used the variable ina view and try to execute the procedure.
i am getting the error :
Must declare the scalar variable "@a".

Ummm.... why don't you just do a SELECT from the view with the correct WHERE clause? You don't need to add a variable to a view...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions