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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-12-02 : 08:19:06
|
| Mark Holloway writes "I am trying to create a dynamic stored procedure that will allow me to select any column from any table based on any values:==========================================CREATE Proc procdynamicsql1@table varchar(20),@field varchar(20),@value varchar(20)as EXEC ( ' SELECT * FROM ' + @table + ' WHERE' + @field + ' = ' + @value)GO==========================================When I try to run it I get an invalid column errorEXEC procdynamicsql1 @table=tblpeople, @field=title, @value='Mr'Can anyone help me out?regardsMark" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-02 : 09:10:00
|
| First off, I can't see the point of this stored proc; I understand your idea, but it is infinitely more flexible to just run SELECT queries to get as much as you need with all the criteria you could ever ask for.But anyway --you need a single quote before and after the @value part. Unless @Field is a number, in which case you don't. So you would have to determine that first before you build your dynamic SQL statement.But if this is an interface used to get data from any table/field/condition in your database it seems a little clunky. Just SELECT what you need!- Jeff |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-02 : 09:24:26
|
| Try EXEC ( ' SELECT * FROM ' + @table + ' WHERE ' + @field + ' = ''' + @value + '''')(space after where).It should implicitly convert from char to int.You are always better off putting into a string so you can display thatdeclare @sql varchar(1000)select @sql = ' SELECT * FROM ' + @table + ' WHERE ' + @field + ' = ''' + @value + ''''exec (@sql)I don't think it's a good idea to have something like this in a system either.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-02 : 09:27:11
|
| You might also want to read up on SQL injection:http://www.sqlteam.com/SearchResults.asp?SearchTerms=sql+injectionHaving a procedure like this is about the most dangerous thing you can do, in addition to being inefficient and pretty useless, IMHO. I completely agree with Jeff and Nigel, don't use this proc. |
|
|
|
|
|
|
Dynamic Stored Procedure Problem