Home Forums SQL Server 7,2000 T-SQL Plugging in a variable column name in a stored procedure RE: Plugging in a variable column name in a stored procedure

  • Annee (8/5/2013)


    Sure.. thanks for the inputs.. what would the dynamic sql syntax look like??

    Appreciate any help with the syntax to accomplish this.. thanks!

    Something like this.

    CREATE PROCEDURE [dbo].[pr_GetVendorInfo]

    (

    @ClientName nVarChar(15),

    @VendorName nVarChar(40),

    @InvoiceType nVarChar(10)

    )

    AS

    declare @SQL nvarchar(2000)

    set @SQL = 'SELECT COUNT(*) FROM AgencySupplierPartner '

    + ' WHERE ClientName = @ClientName AND VendorName = @VendorName '

    if @InvoiceType = 'AcceptStandard'

    set @SQL = @SQL + 'AND AcceptStandard = ''true'''

    if @InvoiceType = 'AcceptSummary'

    set @SQL = @SQL + 'AND AcceptSummary = ''true'''

    print @SQL

    --EXEC sp_executesql @SQL, N'@ClientName nVarChar(15), @VendorName nVarChar(40)', @ClientName = @ClientName, @VendorName = @VendorName

    You may ask yourself, "Why not just handle the @InvoiceType as part of the dynamic string? This seems like it would be a good idea but...you would then be introducing sql injection vulnerability because you would end up executing the parameter and that is what you need to avoid. Handling this with 2 separate statements means there is no sql injection vulnerability here.

    Notice also that I commented out the exec statement. Make sure the code you are generating is actually want you want before executing any dynamic sql.

    _______________________________________________________________

    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/