SQL statement with parameter and EXEC

Question

Post reply

SQL statement with parameter and EXEC

fasttrack

SSCertifiable

Points: 6071
More actions
July 26, 2005 at 10:09 am

#164370

I have a table (n), the table n keeps string SQL statement:
CREATE TABLE N (
ID INT,
strSQL VARCHAR (1000)
)

the table n is:
cols: id strSQL
row1: 1 select * from @tablename
declare @sql varchar (1000)
declare @tablename varchar (50)
set @tablename = 'a'
SELECT @sql = strSQL from n where id = 1
print @sql (result is : select * from @tablename)
exec (@SQL)
of course I have an error.....
My question is:

how can I execute the string with the parameter @tablename directly. without build the string?

Anyone have some suggestions?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

I have no idea why you would be writing code like this, but the replace function works well here.

CREATE TABLE N (

ID INT,

strSQL VARCHAR (1000)

)

insert into N (id, strsql)

select 1, ' select * from @tablename'

declare @sql varchar (1000)

declare @tablename varchar (50)

set @tablename = 'a'

SELECT @sql = replace(strSQL,'@tableName', @tablename) from n where id = 1

print @sql

noeld SSC Guru Points: 96590 More actions · Answer 2

Not sure why you want to do that but:

declare @sql varchar (1000)

declare @tablename varchar (50)

set @tablename = 'a'

SELECT @sql = Replacte(strSQL,'@tablename',@tablename&nbsp from n where id = 1

print @sql --(result is : select * from a)

exec (@SQL)

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Ray M

SSC-Insane

Points: 21093

July 26, 2005 at 10:31 am

#577236

I said what U said.

noeld SSC Guru Points: 96590 More actions · Answer 4

I am in shock!!! These are unbelievable close answers

* Noel

fasttrack SSCertifiable Points: 6071 More actions · Answer 5

Thank to everybody...for your suggestion.

I thought that someone asked "What a strange question !"

There is a reason but it is too long explain ....just some words.....

I'm building a long procedure which make some operation on all tables of a large DB, but the name of these tables are kept in a catalog table, that, also, keeps (in a column) the operation to do (the operation is SQL statement...depending on the table).

Thank again

jimpevans Newbie Points: 5 More actions · Answer 6

I sould like to do somethng similar - but within the WHERE clause of a stored procedure. I need to pass in a list of ID's as a parameter - then use them in the WHERE and cannot seem to figure out how. Someihting like

CREATE PROCEDURE dbo.p_ExampleProc

(

@List varchar(100) -- contains '(1,2,3)'

@Value int

)

AS

UPDATE TableX

SET SomeColumn = @Value

WHERE ID IN (@List)

Thanks for any helpfule ideas

Ray M SSC-Insane Points: 21093 More actions · Answer 7

A long article about what your trying to do.

http://www.sommarskog.se/arrays-in-sql.html

2 ways to do it.

Create a function that performs some sort parsing of the string.

So like

UPDATE TableX

SET SomeColumn = @Value

WHERE ID IN (select number from uf_returnNumberFromString(@List))

or dynamic sql which has plenty of other issues

declare @sql nvarchar (1000)

set @sql = 'UPDATE TableX

SET SomeColumn = ' + ltrim(Str(@Value)) + '

WHERE ID IN (' + @List+ ')'

exec @sql