dynamically creating sql and returning its results into a variable 5

[rewdee]

I want to create a query that returns the results of an ID but I'm passing the table name as a parameter so I'm using code like:

CREATE PROCEDURE [dbo].[SP_GET_NEXT_LARGE_ID]
(
  @TableName varchar(50),
   @ID CHAR(25) OUTPUT,
  @NUMRET INTEGER OUTPUT
)
AS   
DECLARE 
 @SQL_STATEMENT varchar(255),
 @temp int 
BEGIN
  Set @ID = '0000000000000000000000000'
  Set @NumRet = 0  

  Set @SQL_STATEMENT = 'SELECT MAX(id) FROM ' + @TableName + ' WHERE (id LIKE "000%")'
    exec @SQL_STATEMENT
    
    @temp= exec @SQL_STATEMENT

I can't seem to figure out how to get the results of my query into @temp variable. What's the T-SQL code to do this?

Thanks,
Rewdee

 

[donutman]

That does it! I'm adding dynamic SQL to the axis of evil.

Correct me if I'm wrong, but I'm guessing that you have 2 at most 3 tables for which this SP is going to be used? I don't mean to be "testy", but dynamic SQL is generally not a good idea because it doesn't allow the db engine to optimize your SP code. Also, it's not good to create keys that are int rather than char and definately better than varchar.
If you insist on proceding, try this construct.

   CASE @TableName
      WHEN 'table1' THEN SELECT @Temp=MAX(id) 
          FROM table1 WHERE (id LIKE "000%")
      WHEN 'table2' THEN SELECT @Temp=MAX(id) 
          FROM table2 WHERE (id LIKE "000%")
      WHEN 'table3' THEN SELECT @Temp=MAX(id) 
          FROM table3 WHERE (id LIKE "000%")
   END

-Karl


[red] Cursors, triggers and user-defined functions are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ClayG]

The problem is that your dynamic SQL is a separate context (scope) that cannot assign to variables in your calling context. Just like if ProcedureA calls ProcedureB, procedureB cannot assign to variables in procedureA. Like procedures, there is a way to specifically pass 'parameters', using sp_ExecuteSql which is a smarter version of 'exec ..'.

Here is my own example, which returns a value from a dynamic statement into a variable @parm1. The key is the parameter list string, which acts like the variable list in a procedure definition: @parmlist = '@parm1 integer OUTPUT'. I have used the same variable name (parm1) in both contexts, but you dont have to.

declare @parm1 integer
set @parm1 = 0
declare @sqlstm nvarchar(200), @parmlist nvarchar(200)
set @sqlstm = 'select @parm1 = count(*) from control'
set @parmlist = '@parm1 integer OUTPUT'
exec sp_ExecuteSql @sqlstm, @parmlist, @parm1 OUTPUT
select @parm1 as parm1

 

[LokiDba]

Hi there,

Do you have to use dynamic SQL, you could have a table of the following structure.

CREATE TABLE SeqNo(
TabName varchar(255),
CurrentSeq int )

Then when you need a Sequence number for you table have a stored procedure with the following code in it

DECLARE @li_NextSeqNo int

BEGIN TRANSACTION

UPDATE SeqNo
SET @li_NextSeqNo = CurrentSeq = CurrentSeq + 1
WHERE TabName = @pv_TabName -- Parameter passed to sp

IF @@ERROR <> 0
BEGIN

ROLLBACK TRANSACTION

END
ELSE
BEGIN

COMMIT TRANSACTION

END

This has an advantage of using MAX() in that when your doing you SELECT someone could have added a new row with the current value calculated by your MAX().

Hope this is some help

Andy (LokiDBA)

 

[SQLSister]

Donutman

it's not good to create keys that are int rather than char

Don't you mean the reverse? An Int key field is better than a Char key field? Int keys give faster performance on joins.

 

[ESquared]

The other problem with dynamic SQL is that it runs in the security context of the owner of the calling procedure, not the calling user. And that is even more of a problem when you consider SQL injection attacks:

[tt] Set @SQL_STATEMENT = 'SELECT MAX(id) FROM ' + @TableName + ' WHERE (id LIKE "000%")'
exec @SQL_STATEMENT[/tt]


What if the @TableName that is passed in is 'products DROP TABLE Users'?

It would work even if you have highly restrictive permissions set for the calling user.

 

[ESquared]

And if the example I gave doesn't work then this should:

'Products DROP TABLE Users SELECT * FROM Products'

 

[ClayG]

ESquared your statement is inverted.

Dynamic SQL executes with the permissions of the User. Who would be the 'owner' of a piece of dynamic SQL ? Only the user.

It is stored procedures ('static') that execute with the permissions of the owner of the stored procedure, if there is an 'ownership chain' (basically when SP owner also owns the tables).

I have dealt with this in a situation of extensive use of SPs and the ownwership chain to control permissions, but where we wanted to use some dynamic SQL in the SP, it would not work if the user had not been granted access to the objects. Your example of trojan-horse dynamic code is exactly the reason why dynamic SQL (even from an SP) always relies on the users permissions, it cannot use the ownwership chain via the SP.

Actually I think if you are dumb enough to write a SP that accepts part of a dynamic statement from the user without validation, that should be your problem (our problem as developers), but Microsoft apparently disagrees and is protecting the world from incompentent developers in this case.

Finally, the poster asked a specific question, I think its rude to go off into the woods of other issues, that may be of no concern. But false assertions and dynamic SQL push 2 of my buttons.

 

[ESquared]

My mistake! Thanks for correcting me, ClayG. I had it backwards.

As for answering specific questions, we're trying to help rewdee avoid future, big problems, which is definitely not rude.


Rewdee: To answer the original question:

CREATE PROCEDURE [dbo].[SP_GET_NEXT_LARGE_ID]
(
   @TableName nvarchar(50),
   @ID CHAR(25) OUTPUT,
   @NUMRET INTEGER OUTPUT
)
AS   
DECLARE 
   @SQL nvarchar(255),
   @temp int 

   Set @SQL = N'SELECT @OutVal = MAX(ID) FROM ' + @TableName
   EXEC sp_executesql @SQL, N'@OutVal int OUTPUT', @OutVal = @ID OUTPUT

Note the change to nvarchar.

 

[SQLSister]

This is where it is going wrong:

CREATE PROCEDURE [login]
       @indexno  varchar
 AS

try this instead:

CREATE PROCEDURE [login]
       (@indexno  varchar)
 AS

Personally I wouldn't name a stored procedure login because that is a SQL server reserved word.

 

[donutman]

You may want to use varchar, where n is a positive integer indicating the maximum length you will accept. Without it appears to default to varchar(1).
It's also very likely that you really want @index_no to be int rather than varchar. It's extremely misleading to say _no and then use varchar.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

EXEC login @indexno = indexno
GO

This doesn't belong inside the stored procedure. Put the GO first.

 

[ESquared]

I have dealt with this in a situation of extensive use of SPs and the ownwership chain to control permissions, but where we wanted to use some dynamic SQL in the SP, it would not work if the user had not been granted access to the objects. Your example of trojan-horse dynamic code is exactly the reason why dynamic SQL (even from an SP) always relies on the users permissions, it cannot use the ownwership chain via the SP.

I'm curious.... today I tested and confirmed that you can add a linked server which is actually an alias of the same server you add it on. Since you can specify the security context for queries to linked servers, couldn't you use OPENQUERY for 'dynamic SQL' when you are having user-permission problems?

OPENQUERY ( linked_server , 'query' )

Or you can one-off it with OPENROWSET as that allows user and password to be specified. Or OPENDATASOURCE.

OPENROWSET ( 'provider_name' 
    , { 'datasource' ; 'user_id' ; 'password' 
        | 'provider_string' } 
    , { [ catalog. ] [ schema. ] object 
        | 'query' } 
    )

Books Online *does* suggest using OPENROWSET and OPENDATASOURCE infrequently... the best bet is probably setting up the linked server.

 

[smsaji]

Thank you very much, ClayG!! Exactly as I wanted. Wanted to return some min, max values to Crystal report 10.

I tried it and terrific!! ('ve been working on it for past few days till yours came to the rescue) Values come out great to crystal, so that I can highlight the min, max values in crosstab report.

THANK YOU!!!

-saj