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/