Dynamic SQL

Question

Dynamic SQL

astrid 69000

Hall of Fame

Points: 3208
More actions
June 15, 2016 at 2:26 am

#317278

Hi 🙂
I am trying to build a procedure that given a table name will do a select from that table. not only that i am having issues also that i read i need to be careful with SQL Injection and I also have no clue how to prevent that.
here are my sample tables
create table model1
(
id int,
mydata nvarchar (10)
)
insert into model1 (id, mydata) values (1, 'astrid');
insert into model1 (id, mydata) values (2, 'peter');
insert into model1 (id, mydata) values (3, 'netha');
select * from model1
create table model2
(
id int,
mydata nvarchar (10)
)
insert into model2 (id, mydata) values (1, 'astrid');
insert into model2 (id, mydata) values (2, 'peter');
insert into model2 (id, mydata) values (3, 'netha');
select * from model2
create table model3
(
id int,
mydata nvarchar (10)
)
insert into model3 (id, mydata) values (1, 'astrid');
insert into model3 (id, mydata) values (2, 'peter');
insert into model3 (id, mydata) values (3, 'netha');
select * from model3
and this is what i have so far but no no, doesnt want to work :crying:
create procedure DailyReport_sp
@ModelNumber nvarchar(100),
@debug bit = 0
as
Begin
declare @TableName nvarchar(200)
set @TableName = select name from sys.objects where type = 'u' and name = @ModelNumber
set @sqlquery = 'SELECT * FROM ' + @TableName
if @debug = 1 PRINT @sql
end

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Your "PRINT" is printing a variable called "@sql", whilst your statement is being appended to a variable called "@SQLQuery".

This seems like an odd requirement, why do you want to do this?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

BrainDonor SSCoach Points: 19252 More actions · Answer 2

Having set the value for @sqlquery you then need to execute it:

exec sp_executesql @sqlquery

Steve Hall
Linkedin
Blog Site

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 3

i just google it, and it doesn't work

so basically i am asking how to fix the procedure and prevent the sql injection.

thanks!!!

BrainDonor SSCoach Points: 19252 More actions · Answer 4

astrid 69000 (6/15/2016)
i just google it, and it doesn't work
so basically i am asking how to fix the procedure and prevent the sql injection.
thanks!!!

What error do you get that is telling you it won't work?

Steve Hall
Linkedin
Blog Site

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 5

this is the error i get

Msg 156, Level 15, State 1, Procedure general_select, Line 8

Incorrect syntax near the keyword 'select'.

Msg 137, Level 15, State 1, Procedure general_select, Line 9

Must declare the scalar variable "@SQLQuery".

Msg 137, Level 15, State 2, Procedure general_select, Line 10

Must declare the scalar variable "@sql".

but i also dont know if what i am doing is good or not, which is probably bad

BrainDonor SSCoach Points: 19252 More actions · Answer 6

have a look at the differences:

create procedure DailyReport_sp

@ModelNumber nvarchar(100),

@debug bit = 0

as

Begin

declare @TableName nvarchar(200), @sqlquery nvarchar(200)

select @TableName = name from sys.objects where type = 'u' and name = @ModelNumber

set @sqlquery = 'SELECT * FROM ' + @TableName

if @debug = 1 PRINT @sqlquery

exec sp_executesql @sqlquery

end

Steve Hall
Linkedin
Blog Site

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 7

ohh... i didnt declare the one of the variables... shame on me :ermm:

that being said, thanks a bunch for the help 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

astrid 69000 (6/15/2016)
Hi 🙂
I am trying to build a procedure that given a table name will do a select from that table...

Just curious...since EXEC DailyReport_sp 'MyTablename' is exactly equivalent to SELECT * FROM MyTablename, what do you gain from this? What's the bigger picture?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden