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
LVL 3
andrew67Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
and, of course, note that you pass the string of @filewhere as a single string, with no quotes:

getFilesFromFilesTbl @fileWhere = 'exe,cmd,bat'
0
 
Pratima PharandeCommented:
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'"
0
 
andrew67Author Commented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

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

thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
0
All Courses

From novice to tech pro — start learning today.