substituting variable values in new variable

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)

substituting variable values in new variable

Author

Topic

crafteecook
Starting Member

6 Posts

Posted - 2009-02-09 : 11:52:33

When I run the following in SS 2005 Mgt Studio, the value for
shipment_id doesn't show in the print @query statement. Shouldn't this @query variable, when printed out, display with the set value for the variable @shipment_id?

declare @shipment_id int
set @shipment_id = 23
print @shipment_id
declare @query varchar(4000)
select @query = 'declare @myship int
select @myship = @shipment_id
select * from shipment where shipment_id = @shipment_id'
print @query

Thanks for your expertise!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-02-09 : 12:12:06

what do you want to do with the variable @myship?

crafteecook
Starting Member

6 Posts

Posted - 2009-02-09 : 12:22:37

quote:
Originally posted by rohitkumar

what do you want to do with the variable @myship?

If I can pass @shipment_id directly into the variable for @query and have the expanded query print out with the value instead of the variable name, then I don't really need @myship.

Thanks for asking!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:22:41

nope print @query qill just print the string :-
'declare @myship int
select @myship = @shipment_id
select * from shipment where shipment_id = @shipment_id'

because thats what it contains

crafteecook
Starting Member

6 Posts

Posted - 2009-02-09 : 12:40:15

so maybe I'm looking at this wrong?
I want to see exactly what will be executed with the substituted values for the variable in the @query statement.

Please show me code that will do this. Thanks!

quote:
Originally posted by visakh16

nope print @query qill just print the string :-
'declare @myship int
select @myship = @shipment_id
select * from shipment where shipment_id = @shipment_id'

because thats what it contains

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 12:46:08

[code]
declare @shipment_id int
set @shipment_id = 23
print @shipment_id
declare @query varchar(4000)
select @query = 'declare @myship int
select @myship = '+ cast(@shipment_id as varchar(10))
+ 'select * from shipment where shipment_id = '+ cast(@shipment_id as varchar(10))
print (@query)
[/code]

crafteecook
Starting Member

6 Posts

Posted - 2009-02-09 : 12:57:30

THANKS!!!! I REALLY APPRECIATE YOUR ASSISTANCE!

Just a follow up: The query is actually being strung together in pieces. Any time I want to pass a value to the query via a variable, I need to enclose the string without the variable using a single quotes, add + cast(@variable name as varchar(10)) without quotes, and if I want to add more to the query, add a +, then put single quotes around the next piece as well.

BTW, I needed to cast the variable as a varchar due to the fact that it was declared as an integer, but the query was declared as varchar, right?

quote]Originally posted by visakh16


declare @shipment_id int
set @shipment_id = 23
print @shipment_id
declare @query varchar(4000)
select @query = 'declare @myship int 
select @myship = '+ cast(@shipment_id  as varchar(10))
+ 'select * from shipment where shipment_id = '+ cast(@shipment_id  as varchar(10))
print (@query)

[/quote]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 13:00:50

yup...you're absolutely right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources