June 9, 2016 at 3:50 pm
Just thought I'd pose this question. Is there a way to use a table name variable in my stored procedure without using dynamic SQL? Something like:
ALTER PROCEDURE MyStoredProc (@TableName nvarchar(255))
AS
BEGIN
SET NOCOUNT ON
Update @TableName
SET Field1 = 'MyValue'
WHERE ID = 1
RETURN
END
I know I can accomplish this by using dynamic SQL, but just trying to avoid that if I can. We don't know the table name until runtime as it is created as a global temp table by another application. The name is passed to this stored proc so that it can work with the global temp table while the other application is in scope. If this can be done that would be great. If not, thenI guess I'll have to resort to dynamic SQL. I'm using SQL Server 2008.
Thanks!
Strick
June 10, 2016 at 8:15 am
No, but you can pass a table variable into a stored procedure. Can you modify the other process to create a table variable rather than a temp table?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 10, 2016 at 8:52 am
Hi Drew,
Thanks for your response. Unfortunately no. The other process is designed that way because there are other processes (much like this stored procedure) that will be working with the temp table while it is in scope.
Strick
June 10, 2016 at 9:25 am
stricknyn (6/10/2016)
Hi Drew,Thanks for your response. Unfortunately no. The other process is designed that way because there are other processes (much like this stored procedure) that will be working with the temp table while it is in scope.
Strick
Don't rule it out just because other processes are using it. You can still create the temp table and then insert the rows from the temp table into a table variable which you then pass to the dependent process, although you may also want to consider rewriting all of the other processes in a similar fashion, because there are problems with global temp tables that you don't encounter with local temp tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 12, 2016 at 4:43 pm
stricknyn (6/10/2016)
Hi Drew,Thanks for your response. Unfortunately no. The other process is designed that way because there are other processes (much like this stored procedure) that will be working with the temp table while it is in scope.
Strick
Have a permanent "staging data" table.
It must have a "Session ID" column.
It better be a first column of a clustered index on that table.
Insert data into the table with newly generated SessionID and pass that ID to other procedures for further processing.
Delete the data relevant to a SessionID ones the processing is done.
_____________
Code for TallyGenerator
June 15, 2016 at 7:27 am
What about this ?
Create Table test12 (i int, j int)
insert into test12
Select 1,5
Create procedure test
(@tablename varchar(10),
@filedval int,
@where int)
as
Begin
declare @String varchar(200)
set @String = 'Update '+ @tablename +' Set j = '+ Convert(varchar(10),@filedval) +' Where j ='+ Convert(varchar(10),@where)
print @String
exec @String
end;
exec test 'test12','7','5'
June 15, 2016 at 7:55 am
yuvipoy (6/15/2016)
What about this ?Create Table test12 (i int, j int)
insert into test12
Select 1,5
Create procedure test
(@tablename varchar(10),
@filedval int,
@where int)
as
Begin
declare @String varchar(200)
set @String = 'Update '+ @tablename +' Set j = '+ Convert(varchar(10),@filedval) +' Where j ='+ Convert(varchar(10),@where)
print @String
exec @String
end;
exec test 'test12','7','5'
By the time you have done this with dynamic sql like this there really isn't much point left in having this be a procedure. There are only a few characters not part of the parameters. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 15, 2016 at 8:44 am
Sean Lange (6/15/2016)
By the time you have done this with dynamic sql like this there really isn't much point left in having this be a procedure. There are only a few characters not part of the parameters. 😛
Sean you are right, i gave the additional column to user to understand the concept :smooooth:.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply