Building SQL with T-SQL

  • In VB it is possible to build a SQL statement based on criteria. But this is largely because SQL statements are strings. These strings are then passed to SQL Server for execution.

    I would like to be able to do something similar with SQL Server stored procedures. Unfortunately, when I look at the SQL in a stored procedure, the SQL statement is not a string. So it doesn't look possible to do this in a stored procedure. Does anyone know how to build SQL statements within a stored procedure?

  • You can do it with Exec(). Downsides are you dont get a compiled query plan, security is evaluated at run time, presents a potential security flaw via an injection attack. Still, at times it makes sense.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Thanks Andy.

    It's too bad! That's one less reason to use stored procedures - at least for single record inserts.

    It looks like the code will have to be a method of a table class in VB instead. Would you agree with this? Or is there a better option?

    Thanks,

    Mike

  • I don't actually agree with putting the insert in the application code. At least not if the only reason for it is the lack of a stored query plan.

    If you would build and execute the query from your app, you won't be able to gain any performance profit either. The query you are executing there won't have any stored execution plan either.

  • You can still use SP's the key is create a security layer for the incoming code.

    Ex.

    CREATE PROC sp_DynamSQL

    @keywords VARCHAR(8000),

    @col INT

    AS

    SET NOCOUNT ON

    if CHARINDEX('TRUNCATE', @keywords) > 0 OR

    CHARINDEX('DROP', @keywords) > 0 OR

    CHARINDEX('CREATE', @keywords) > 0 OR

    CHARINDEX('DELETE', @keywords) > 0 OR

    CHARINDEX('SELECT', @keywords) > 0 OR

    CHARINDEX('UPDATE', @keywords) > 0 OR

    CHARINDEX('EXEC', @keywords) > 0 OR

    CHARINDEX('sp_', @keywords) > 0 /*OR

    anything else that could potentially compromise your databases*/

    BEGIN

    /*Log details of connection here*/

    RASIERROR('I'm sorry but you can bug off.' 16, -1)

    RETURN

    END

    By looking for specific potential problems you can effectively stop the injection attack.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Also it depends on how you plan to build dynamic sql in your sp. If the user is not submitting the sql to the sp, but your sp generates dynamic sql based on data in a SQL table, then there may be no need for the additional security layer.

    -------------------------

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • Hi,

    strINSERT = "INSERT INTO tblPerson ( "

    strSELECT = " SELECT "

    'Gender

    If cboGender <> "" Then

    strINSERT = strINSERT & "Gender"

    strSELECT = strSELECT & QUOTES & cboGender & QUOTES

    End If

    'First Name

    If txtFirstName <> "" Then

    strINSERT = strINSERT & ", FirstName"

    strSELECT = strSELECT & ", " & QUOTES & txtFirstName & QUOTES

    End If

    'Last Name

    If txtLastName <> "" Then

    strINSERT = strINSERT & ", LastName"

    strSELECT = strSELECT & ", " & QUOTES & Me!txtLastName & QUOTES

    End If

    'Last part of the "INSERT" piece

    strSQL = strINSERT & strSELECT

    The above VB code is an example of what I am doing in the front end. The reason I am doing it this way is because some fields in the table are not required, so it is not necessary to force the user to provide a value. In an INSERT SELECT statement, if you designate a field, you must also provide a value; otherwise you get an error. However, it occurred to me that I might do the same thing in a stored procedure. I would have a parameter for all the fields and only include a field in the INSERT SELECT statement if the parameter was not null.

    However, when I looked at sample stored procedures, I noticed that the SQL statements were not strings and therefore I might not be able to do this kind of thing there. Perhaps I should do it in the middle tier. This is what I was thinking.

    Is there another way to do this?

    Mike

  • Actually in Procs you can set default values and work with them.

    Ex.

    CREATE PROC sp_InData

    @FirstName = NULL, Default value of NULL

    @LastName --No default is required.

    AS

    INSERT INTO

    tblUser (FName, LName)

    VALUES (@FirstName, @LastName)

    SELECT * FROM tblUser WHERE

    (CASE WHEN @FirstName IS NULL THEN '' ELSE FName END = CASE WHEN @FirstName IS NULL THEN '' ELSE @FirstName END ) AND LName = @LastName

    Hope this helps.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Is this what you are looking for?

    CREATE PROCEDURE BuildDynamic

    (

    @cboGender char(1),

    @txtFirstName varchar(10) = '',

    @txtLastName varchar(10) = ''

    ) AS

    DECLARE

    @strSQL varchar(2000),

    @strINSERT varchar(1000),

    @strSELECT varchar(1000)

    SET @strINSERT = 'INSERT INTO tblPerson ('

    SET @strSELECT = + CHAR(10) + 'SELECT '

    --Gender

    IF @cboGender <> ''

    BEGIN

    SET @strINSERT = @strINSERT + 'Gender'

    SET @strSELECT = @strSELECT + '''' + @cboGender + ''''

    END

    --First Name

    IF @txtFirstName <> ''

    BEGIN

    SET @strINSERT = @strINSERT + ', FirstName'

    SET @strSELECT = @strSELECT + ', ''' + @txtFirstName + ''''

    END

    --Last Name

    IF @txtLastName <> ''

    BEGIN

    SET @strINSERT = @strINSERT + ', LastName'

    SET @strSELECT = @strSELECT + ', ''' + @txtLastName + ''''

    END

    --Last part of the "INSERT" piece

    SET @strSQL = @strINSERT + ')' + @strSELECT

    PRINT @strSQL

    EXEC (@strSQL)

    Robert Marda

    SQL Server will deliver its data any way you want it

    when you give your SQL Programmer enough developing time.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Hi,

    I will have to try the last two suggestions and then let everyone know how they worked out.

    Thanks,

    Mike

  • Hey Mike,

    is this table being used a lot? If so any reason why you're not creating and maintaining an ADO recordset and doing a .addnew and a .update.

    Cheers,

    mike

  • use sp_executeSql - if you fully qualify the dynamic sql in the SP SQL Server will precompile and bind if you rexecute the same statement multiple times...

    --woody

    C. Woody Butler, MCP

    cwbutler@cebec.com


    C. Woody Butler, MCP
    cwbutler@cwoodybutler.com

  • Be -very- careful about the "string-building" you do. Your first/last name strings [should] be OK as is, but if you give enough elbow room in the length of a variable, a potential first name string could be:

    ' truncate table tblPerson

    which would, in effect, build your SQL string to look like:

    INSERT INTO tblPerson (Gender, Firstname)

    SELECT 'M', ''

    TRUNCATE TABLE tblPerson

    Think about it.

    I like Antares686's check, though. Good idea.

    Cheers,

    Ken

  • To avoid the issue of people executing their own code you can use this code for each variable that is non numeric:

    SET @txtLastName = REPLACE(@txtLastName,'''','''''')

    Robert Marda

    SQL Server will deliver its data any way you want it

    when you give your SQL Programmer enough developing time.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • Hi folks,

    I want to thank you all for your input on this subject. My only objective in this case was to create an INSERT stored procedure that would handle the situations in which a field could hold a NULL and the parameters passed for them were NULL. Antares' suggestion of putting in NULL defaults for unrequired fields sounded very simple and straight forward; so I gave it a try. I worked very well.

    Thanks again,

    Mike

Viewing 15 posts - 1 through 15 (of 17 total)

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