Passing parameters with '@' in the code.

Question

Post reply

Passing parameters with '@' in the code.

zhesley

Right there with Babe

Points: 739
More actions
November 6, 2003 at 4:55 am

#83542

I'd like to pass a parameter into my code but am having problems with the syntax for passing parameters into code with the '@' sign.
For example, for the code
DECLARE @OCT_ID varchar(50)
it would be nice to run my stored procedure with
sp_anyname 'JULY'
and for the DECLARE command to change to
DECLARE @JULY_ID varchar(50)
I've tried putting the '@' in quotes but this does not appear to work.
Thanks
Zoltan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Not sure if I understand everything

You're not after how to pass a parameter with the @ sign, right

You're looking for a way to dynamically change a variable declaration in a stored procedure?

If so, why?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

nigelrivett SSCertifiable Points: 5472 More actions · Answer 2

???

Why do you care what a variable is named?

Why do you want it to be based on a parameter?

Cursors never.

DTS - only when needed and never to control.

Cursors never.
DTS - only when needed and never to control.

zhesley Right there with Babe Points: 739 More actions · Answer 3

Why?

The same code is run again and again but for

different months.

At the top of the code I set the month

and can thus run the same code easily for the appropriate month.

I just have to change

set @Month ='JULY' to

set @Month ='OCT' and all occurences of

@Month in the code change to OCT.

However, when I come to the cursor part of the code and use code like this:

DECLARE @JULY_ID varchar(50)

FETCH NEXT FROM curFolders INTO @JULY_ID

I have to change this part of the code manually.

I was hoping there was a way I could dynamically change this bit of the code too.

Hope this makes sense!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Not sure what your whole code looks like, but have you considered the use of DATENAME to determine the month?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Sorry, for the typo

Edited by - Frank Kalis on 11/06/2003 06:17:32 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 5

This looks like it should be as simple as a terminology change in the code.

Why can't you rename @JULY_ID to @MONTH_ID?

What are we missing?

R David Francis

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 6

Sounds like "Zoltan" doesn't see that this is an off-the-wall objective....

it's a bit like saying...(just because today is FRIDAY)...I want all my references to SELECT @dt = GETDATE() to dynamically change to SELECT @friday = GETDATE()...and tomorrow to say SELECT @SATURDAY = GETDATE().

It makes no difference to the execution of the code....but totally defeats the purpose of defining an abstract variable @dt (to hold any date)

Zoltan....I'd give up on this one....

You need to differentiate between the "how the code reads with variables coded"...and how "the code executes with values in the variables"

Also.....I hope you're staying as far away from "CURSORS" as you possibly can...unless you have absolutely no other alternative....

The performance difference between using a cursor-based solution and a set-based solution can be staggering (and the difference is very very rarely in favour of the cursor)

Antares686 SSC Guru Points: 125444 More actions · Answer 7

quote:
This looks like it should be as simple as a terminology change in the code.
Why can't you rename @JULY_ID to @MONTH_ID?
What are we missing?
R David Francis

I agree with this. With what you are saying now you would have to use dynamic SQL to build your code but you hit a lot of draw backs there. Seems like more hassle than you really need. Is there a specific reason you want the variable to be named that way?

zhesley Right there with Babe Points: 739 More actions · Answer 8

I wanted to add the @ symbol to dynamically create code like

set @strSQL = 'DECLARE @' + @VARIABLE + ' varchar(50)'

exec (@strSQL)

to create :

DECLARE @JULY varchar(50)

dynamically

This appears to work. There must be another issue in my code that meant this did not work the other day.

Thanks for all the feedback - much appreciated.

Edited by - zhesley on 11/07/2003 06:25:41 AM

lambje SSCrazy Points: 2085 More actions · Answer 9

Zoltan,

I think you missed the point. You really shouldn't name your variables like that. You should do as a number of folks have pointed out. Have a generic name, @month_num, assign the month to it and then pass that number through to your next proc.

Good luck,

Jeff