Setting a variable... - 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 2005 Forums

Transact-SQL (2005)

Setting a variable...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-06-06 : 12:05:28

Hi I have a problem setting a variable.

If i hard code it, it works, i.e:

declare @Pages int

set @Pages = (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL 
where DB in ('PG') and len(PageCount) > 0 and PageCount is not null)

select @Pages

Result: 1000

Now, why I try to run this using variables I return: NULL

declare @sqlstring nvarchar(1000),
		@DB_Selection nvarchar(1000),
		@Pages int

set @DB_Selection = 'where DB in (''PG'')'

set @sqlstring = 'declare @Pages int set @Pages = (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL 
' + @DB_Selection  + ' and len(PageCount) > 0 and PageCount is not null)'

Exec sp_executesql @sqlstring  

select @Pages

Results: NULL

Can anybody let me know what the problem is? I need to use variables to execute, therefore cannot hard code.

Many Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-06 : 12:18:17

The variable which you declare inside the dynamic sql cease to exist outside scope of dynamic sql. You are declaring variable @pages inside dynamic sql and assigning value to it, whereas in the

SELECT @Pages

statement, the outer @Pages variable is referred which is not initialized, hence you are getting result as NULL.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-06-06 : 17:06:29

But how would i fix my code, If I remove the Declaration from the @sqlstring string I get an error saying that my variable is not declared......

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 17:27:25

Perhaps the dynamic SQL could be avoided if we have more info..but just to fix your code here's what might work..


declare @sqlstring nvarchar(1000),
		@DB_Selection nvarchar(1000),

set @DB_Selection = 'where DB in (''PG'')'

Create table #tmp (PAges int)
set @sqlstring = 'Insert into #tmp (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL ' + @DB_Selection  + ' and len(PageCount) > 0 and PageCount is not null)'
Select * from #tmp
Drop table #tmp

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-06-06 : 18:32:32

Thanks. Sorry i didn't explain clearly, but the main purpose of my script it to get a the value of the count from the query into a variable: @Pages

I.e. I want all the pages for that query to appear in @pages, therefore my final results from

select @pages

should be 1000. (if there are 1000 pages in my table)

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 18:37:17

Did'nt the SELECT * FROM #tmp return a 1000?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-07 : 04:10:59

Get count value from dynamic sql can be easily accomplished by using OUTPUT variable in sp_executesql. For more information, see books online for sp_executesql, there is a nice example on how to do that.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-06-07 : 05:15:53

quote:
Originally posted by dinakar

Did'nt the SELECT * FROM #tmp return a 1000?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

No that didn't work...

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-06-07 : 05:19:36

quote:
Originally posted by dnf999

Hi I have a problem setting a variable.

If i hard code it, it works, i.e:

declare @Pages int

set @Pages = (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL
where DB in ('PG') and len(PageCount) > 0 and PageCount is not null)

select @Pages

Result: 1000

Now, why I try to run this using variables I return: NULL

declare @sqlstring nvarchar(1000),
@DB_Selection nvarchar(1000),
@Pages int

set @DB_Selection = 'where DB in (''PG'')'

set @sqlstring = 'declare @Pages int set @Pages = (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL
' + @DB_Selection + ' and len(PageCount) > 0 and PageCount is not null)'

Exec sp_executesql @sqlstring

select @Pages

Results: NULL

Can anybody let me know what the problem is? I need to use variables to execute, therefore cannot hard code.

Many Thanks!

try this ...

declare @sqlstring nvarchar(1000),
@DB_Selection nvarchar(1000),
@Pages int

set @DB_Selection = 'where DB in (''PG'')'

set @sqlstring = 'select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL
' + @DB_Selection + ' and len(PageCount) > 0 and PageCount is not null'

Exec sp_executesql @sqlstring

as suggested by Harsh, use OUTPUT param here and get it in return with OUTPUT param (considered, u ll b using it in SP)

let us know,

Mahesh

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-06-07 : 05:37:08

I managed to fix it by using the sp_executesql output function.

Many thanks!!

set @SqlString = N'select @Pages = (select sum(cast(PageCount as numeric)) as [Number of Pages] from DB_ALL 
' + @DB_Selection  + ' and len(PageCount) > 0 and PageCount is not null)'
Exec sp_executesql @SqlString, N'@Pages int output', @Pages Output

select @Pages

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-07 : 05:42:35

Great!

That's the correct approach. All the best for future.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-06-07 : 05:48:32

quote:
Originally posted by harsh_athalye

Great!

That's the correct approach. All the best for future.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

But Harsh, as a performance concern, I will prefer to use Static SQL and get the resultset, instead of using Dynamic SQL. Ya, it is small qry. so there will be negligible diff in the same qry written in Static and Dynamic SQL. What say?

Mahesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-07 : 05:58:42

I would prefer not to use D-sql as far as possible. But as in this case, since the table name itself is dynamically built (even though it is bad and not advisable), there is no better alternative to D-sql.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 10:16:25

When you dont pass object name as paramter, dont use dynamic sql
http://www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources