Eliminate Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Eliminate Dynamic SQL

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-10-23 : 18:05:29

I would like to eliminate the dynamic sql from this procedure,
how can i get the in clause to run without building the sql statement at run time?

exec [dbo].[usp_monitor_FreightExemptPH]'KS', "'4116','0060'"

calls
create PROCEDURE [dbo].[usp_monitor_ProductSpecificTax]
/*****************************************************************************************************
FILE NAME : usp_monitor_ProductSpecificTax.
PURPOSE : THIS STORED PROCEDURE RETURNS THE TAX RATE FOR INDIVIDUAL PRODUCTS
AUTHOR : INFOSYS
SAMPLE EXECUTION : EXEC usp_monitor_ProductSpecificTax '''5920'',''5166''',
'''KS''','''10'''
HISTORY :
------------------------------------------------------------------------------------------------------
VER.NO. BY DATE MODIFICATION
------------------------------------------------------------------------------------------------------
0.0 INFOSYS 21 AUG 2033 INTIAL VERSION
******************************************************************************************************/

@PRODS VARCHAR(1000),
@COUNTRYCODE CHAR(4),
@REGIONCODE CHAR(4)
AS
BEGIN

IF (@PRODS IS NOT NULL) AND (LTRIM(@PRODS) <> '')
AND (@COUNTRYCODE IS NOT NULL) AND (LTRIM(@COUNTRYCODE) <> '')
AND (@REGIONCODE IS NOT NULL) AND (LTRIM(@REGIONCODE) <> '')

BEGIN

DECLARE @SQL VARCHAR(4000)
--FORM THE SQL WHICH WILL RETURN THE TAX RATE FOR THE COUNTRYCODE,REGION AND PRODUCT LIST COMBINATION
SET @SQL = 'SELECT ISNULL(PROD_RATE_1,0) AS PROD_RATE_1,ISNULL(PROD_RATE_2,0) AS PROD_RATE_2,
ISNULL(EFF_DATE_1,'''') AS EFF_DATE_1,ISNULL(EFF_DATE_2,'''') AS EFF_DATE_2,ISNULL(PROD,'''') AS PROD
FROM DBO.DISTR_TX_NONSTD_PROD X WITH (NOLOCK)
WHERE X.COUNTRY = ' + @COUNTRYCODE +
' AND X.REGION= ' + @REGIONCODE +
' AND X.PROD IN (' + @PRODS + ')'

SELECT ISNULL(PROD_RATE_1,0) AS PROD_RATE_1,ISNULL(PROD_RATE_2,0) AS PROD_RATE_2,
ISNULL(EFF_DATE_1,'') AS EFF_DATE_1,ISNULL(EFF_DATE_2,'') AS EFF_DATE_2,ISNULL(PROD,'') AS PROD
FROM DBO.DISTR_TX_NONSTD_PROD X WITH (NOLOCK)
WHERE X.COUNTRY = 'KS' AND X.REGION= '10' AND X.PROD IN ('4116','0060')

PRINT @SQL
--EXECUTE THE SQL
EXEC(@SQL)

IF(@@ERROR > 0)
--IF ANY ERROR OCCURS RAISE AN ERROR AND LOG IT
BEGIN
RAISERROR ('ERROR EXECUTING THE SQL IN STORED PROCEDURE: usp_monitor_ProductSpecificTax' ,16, 1)

END
END
ELSE
BEGIN
--'RAISE ERROR AS THE INPUT PASSED IS INVALID
RAISERROR ('THE INPUT PRODS,COUNTRYCODE OR REGIONCODE IS BLANK OR NULL IN STORED PROCEDURE: usp_monitor_ProductSpecificTax.',16, 1)
END

END

You can do anything at www.zombo.com

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2008-10-23 : 19:07:10

Take a look at PIVOT in SQL 2005 BOL. That will do the trick. If your version is older than 2005, then a auxiallary table will do the trick.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 02:06:12

just use like below

SELECT ISNULL(PROD_RATE_1,0) AS PROD_RATE_1,ISNULL(PROD_RATE_2,0) AS PROD_RATE_2, 
ISNULL(EFF_DATE_1,'''') AS EFF_DATE_1,ISNULL(EFF_DATE_2,'''') AS EFF_DATE_2,ISNULL(PROD,'''') AS PROD 
FROM DBO.DISTR_TX_NONSTD_PROD X WITH (NOLOCK)
WHERE X.COUNTRY =  @COUNTRYCODE 
 AND X.REGION=  @REGIONCODE 
AND ','+ @PRODS + ',' LIKE '%,'+ CAST(X.PROD AS varchar(10)) + ',%'

also make sure EFF_DATE_1,EFF_DATE_2 etc are of varchar type else ISNULL(EFF_DATE_1,'''') will cause error as '''' cant be casted to datetime type.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-24 : 09:47:20

The reason for the dynamic sql is the AND X.PROD IN (' + @PRODS + '), right? In that case you can add a function that will convert you comma separated string to a table-variable using a table-valued function, and then do a regular join (don't know if this is the most efficient funtion but it does the trick):

ALTER FUNCTION [dbo].[CSV2Int](@CSV varchar(8000), @Delimiter varchar(2))
RETURNS @myTable table (Number int)
AS
BEGIN

  DECLARE @Number varchar(10)
  
  WHILE (CHARINDEX(@Delimiter, @CSV)) > 0
    BEGIN
      SET @Number = LEFT(@CSV, CHARINDEX(@Delimiter, @CSV)-1)
      INSERT INTO @myTable SELECT RTRIM(LTRIM(@Number))
      SET @CSV = RIGHT(@CSV, LEN(@CSV)-CHARINDEX(@Delimiter, @CSV))
    END
  INSERT INTO @myTable SELECT RTRIM(LTRIM(@CSV))

RETURN 

END

Then do a join like this:

SELECT ...
FROM DBO.DISTR_TX_NONSTD_PROD X WITH (NOLOCK)
  INNER JOIN dbo.CSV2Int(@PRODS, ',') b
    ON X.PROD = b.Number
WHERE X.COUNTRY = @COUNTRYCODE
  AND X.REGION= @REGIONCODE

- Lumbago

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 09:59:27

Hi Lumbago,

!SNAP!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113108

-------------
Charlie

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-24 : 10:16:40

Snap? What does that mean...?

- Lumbago

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-24 : 11:18:46

Sorry -- I posted almost exactly the same fix in the link.

I was referring to the game of cards where you shout "snap" when you put the same card down as another player.

Regards,

-------------
Charlie

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-27 : 03:57:40

Ah...read your post and realised that it was the same solution but I've never played that game of cards so I didn't understand the meaning of snap (last time I checked snap was a pop/techno gruoup back in the good'ol 90's

)

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources