dynamically assigning a value to a variable

[Guest_imported]

I need to be able to dynamically run the following statement as part of a Stored Procedure. The value of the count needs to be passed to the @test variable:

declare @test char(10)
select @test = count(*) from @tablename where @fieldname in ('FALSE','TRUE')
exec @test


In order to do this I ran this test statement:

declare @tablename varchar(100)
set @table = 'test_table
declare @fieldname varchar(100)
set @fieldname = 'test_field'
declare @test char(10)
declare @script varchar(255)

set @script =
('select @test = count(*) from ' + @tablename + ' where rtrim(' + @fieldname + ') in (''FALSE'',''TRUE'')')

print @script
print char(013)
execute @script


when printing the @script variable, I get the following:

select @test = count(*) from test_table where rtrim(test_field) in ('FALSE','TRUE')

which is what I want, however when the @script string is executed, I get the following error message:

Could not find stored procedure 'select @test = count(*) from test_table where rtrim(test_field) in ('FALSE','TRUE')'.


Can anyone help, as it is driving me crazy ?

 

[salma]

You need to use the stored procedure sp_execute_sql instead of the execute command

This stored procedure will simply run any SQL statement that is passed to it. The only thing to bear in mind is that if you pass a variable, it has to be of type unicode

Modified Script
declare @tablename varchar(100)
set @table = 'test_table
declare @fieldname varchar(100)
set @fieldname = 'test_field'
declare @test char(10)
declare @script nvarchar(255) -- specify @script as unicode

set @script =
('select @test = count(*) from ' + @tablename + ' where rtrim(' + @fieldname + ') in (''FALSE'',''TRUE'')')

print @script
print char(013)
sp_execute_sql @script -- use the sp to exec the string

hope this helps
Salma

 

[Guest_imported]

Unfortunately, I can't get this to work either. The following error message appears :

Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'sp_executesql'.

 

[salma]

not sure if it's a typo in your mail but the stored procedure is named sp_execute_sql (note the extra underscore).

Gets me every time

 

[CBT]

related question.
declare the string as nvarchar is limited to 4000 characters. Is there a way to get round this.
Would declare as ntext work, if so please give an example

Cheers

 

[tlbroadbent]

Check thread183-142446. I just posted an answer that shows how to return a value from dynamic SQL using sp_executesql. Note: there is only one underscore in the SP name. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[seekwall]

Ok, I have looked at other threads to see if I can overcome this issue. I have tried but failed miserably. As you can see from the following procedure, I have used sp_executesql to run the @script variable.

The problem is that is can't get the @field_count variable to be populated with any value, which suggests to me that the sp_executesql statement isn't being executed correctly. I have looked at Books On-Line for help, and the information is not particularly intuitive to my needs.

I could really do with some help

Thanks



CREATE PROCEDURE my_proc @prm_table varchar(800) as

set nocount on

declare @fieldname varchar(100)
declare @field_count int
declare @script nvarchar(4000)

set @script = 'select @field_count = count(*) from ' + rtrim(@prm_table) + ' where rtrim(' + @fieldname + ') in (''FALSE'',''TRUE'')'

declare my_cursor cursor fast_forward
for select column_name from information_schema.columns where rtrim(table_name) = rtrim(@prm_table) and data_type like '%char%'

open my_cursor
fetch next from my_cursor into @fieldname

while @@fetch_status =0
begin

EXECUTE sp_executesql @script, N'@field_count int OUTPUT, @prm_table varchar(800), @fieldname varchar(100)',@field_count, @prm_table,@fieldname

print @script

if @field_count > 1
begin
print (@fieldname + ' = true')
end
else
print (@fieldname + ' = false')

fetch next from my_cursor into @fieldname
end --

close my_cursor
deallocate my_cursor

 

[tlbroadbent]

CREATE PROCEDURE my_proc @prm_table varchar(800) as

set nocount on

declare @fieldname varchar(100)
declare @field_count int
declare @script nvarchar(4000)

declare my_cursor cursor fast_forward
for select column_name from information_schema.columns where rtrim(table_name) = rtrim(@prm_table) and data_type like '%char%'

open my_cursor
fetch next from my_cursor into @fieldname

while @@fetch_status =0
begin

Set @script =
'Select @field_count = count(*) From ' +
rtrim(@prm_table) +
' where ' + rtrim(@fieldname) +
' In (''FALSE'',''TRUE'')'

EXECUTE sp_executesql @script,
N'@field_count int OUTPUT',
@field_count OUTPUT

.
.
. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[seekwall]

That's the baby - cheers