asked on

Parameter in stored procedure

Hi all i have the following stored procedure

CREATE PROCEDURE getFilesFromFilesTbl
(
      @filetype varchar(2) = NULL,
      @fileWhere varchar(255) = NULL
)
AS
IF @filetype = 6 OR @filetype = 7
      SET @fileWhere = " 'asx'"

IF @filetype = 4
      SET @fileWhere = "'doc', 'pdf', 'ppt', 'pps', 'xls', 'htm', 'txt'"

SELECT filepath, filesize, SUBSTRING(filepath, LEN(filepath) - 2, LEN(filepath)) AS Expr1
FROM sitefiles
WHERE (LOWER(SUBSTRING(filepath, LEN(filepath) - 2, LEN(filepath))) IN ( @fileWhere ))
ORDER BY dateadded DESC
GO

however when i call it EXEC getFileFromTbl 4

its failing to put my extentions into the in bit for some reason

can anyone tell me what im doing wrong, it works fine if i just write the sql out

thanks

Pratima

CREATE PROCEDURE getFilesFromFilesTbl
(
@filetype varchar(2) = NULL,
@fileWhere varchar(255) = NULL
)
AS
IF @filetype = '6' OR @filetype =' 7'
SET @fileWhere = " 'asx'"

IF @filetype = '4'
SET @fileWhere = "'doc', 'pdf', 'ppt', 'pps', 'xls', 'htm', 'txt'"

andrew67

ASKER

is that not what i already have??

the problem i have is that in the actual query it doesn seem to be putting the value of @filetype into it

WHERE (LOWER(SUBSTRING(filepath, LEN(filepath) - 2, LEN(filepath))) IN ( @fileWhere ))

so when ever i run the query i never get a result set

Guy Hengel [angelIII / a3]

IN ( @fileWhere ))

won't work.

create the below function:

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          ) 
     DECLARE @Value varchar(30), @Pos int 
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1) 
     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1) 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END

Open in new window

Guy Hengel [angelIII / a3]

and change your procedure like this:

CREATE PROCEDURE getFilesFromFilesTbl 
(
      @filetype varchar(2) = NULL,
      @fileWhere varchar(255) = NULL
)
AS
IF @filetype = 6 OR @filetype = 7
      SET @fileWhere = 'asx' 
IF @filetype = 4
      SET @fileWhere = 'doc,pdf,ppt,pps,xls,htm,txt' 
SELECT     filepath, filesize, SUBSTRING(filepath, LEN(filepath) - 2, LEN(filepath)) AS Expr1
FROM         sitefiles
WHERE     LOWER(RIGHT(filepath, 3)) IN ( SELECT value FROM dbo.ParmsToList(@fileWhere) )
ORDER BY dateadded DESC

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrew67

ASKER

works a treate thanks not sure why i couldnt just put a value into the IN() bit but never mind

thanks

Guy Hengel [angelIII / a3]

>not sure why i couldnt just put a value into the IN() bit
because
IN ( @fileWhere ))

will evaluate @fileWhere as a single value, and not as a list of values.