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 |
|
crchisholm
Starting Member
2 Posts |
 Posted - 2005-07-17 : 18:16:47
|
| HelloI can't figure out how to setup a dynamic query and return the results to another stored procedure for use.I have several views with a column "userValidation" and the names of these views are held in a table called "viewsTable" that looks like this ... create table viewNames (repid int, viewname varchar(100))data might be 1,"vwThisOne"2,"vwThatOne"and the views themselves each have a column called "userValidation"I need to create code that will return the value of the "userValidation" column by sending it the view name. It must be returned to another stored procedure (spMainProcedure below) and I can't figure out how to do it...If I have a procedure... Create procedure spMainProcedure @repid int as declare @viewName varchar(100), @userValidation varchar(20) -- look up the view name select @viewName = viewname from viewsTable where repid=@repid -- now I need some code to look up the value so I can use it. For instance, if I could do this (I know it won't work) set @userValidation=someFunction('select userValidation from ' + @viewName ) -- of course the problem is that I don't know how to create a function like this. ...remander of procedure could use @userValidation as needed.go In other words, I need something that works like OpenQuery except on the local instance.Any help would be GREATLY appriciated!!Charlie |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-17 : 19:03:44
|
Check out the sp_executeSQL system stored proc. It will let you return parameter(s) to your calling procedure.DECLARE @cSQL nvarchar(200)SET @cSQL = N'SELECT @userValidation FROM ' + @viewName + ' WHERE repid = ' + CONVERT(varchar, @repid)EXEC sp_executeSQL @cSQL, N'@userValidation varchar(10) OUT', @userValidation OUT That's the basic gist of it. Check out BOL for more info (and to double-check my syntax!!)Tim |
 |
|
|
crchisholm
Starting Member
2 Posts |
Posted - 2005-07-17 : 20:01:20
|
You nailed it!! I inserted the code you suggested with only some minor modifications and it worked. Thank you. I had been chasing that one all weekend. I don't think I would have figured it out from BOL, though. My version mentions nothing about the output variable. I guess I should have been able to assume that, but probably wouldn't have.Thanks AgainCharliequote:
Originally posted by timmy
Check out the sp_executeSQL system stored proc. It will let you return parameter(s) to your calling procedure.DECLARE @cSQL nvarchar(200)SET @cSQL = N'SELECT @userValidation FROM ' + @viewName + ' WHERE repid = ' + CONVERT(varchar, @repid)EXEC sp_executeSQL @cSQL, N'@userValidation varchar(10) OUT', @userValidation OUT That's the basic gist of it. Check out BOL for more info (and to double-check my syntax!!)Tim
|
|
|
|
|
|
|
|
|
dynamic queries