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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.