Help with Stored Procedure

Results 1 to 2 of 2

Thread: Help with Stored Procedure

  1. #1
    Join Date
    Dec 1969
    Posts
    940

    Default Help with Stored Procedure

    Stored Procedure below;<BR><BR>The problem is that it will not run correctly when I pass it a list of values to use for the in filter. Like so:<BR><BR>sp_respana &#03901/01/1999&#039,&#03902/01/1999&#039,&#039"ATC","GEN"&#039<BR><BR>I have tried to format the above in many ways but it does not bring back any results. I know that there is data in the table that meets this criteria. It will however return a proper result when I have only one value that I pass to the procedure, like this:<BR><BR>sp_respana &#03901/01/1999&#039,&#03902/01/1999&#039,&#039"ATC"&#039<BR><BR>The only way that I have made this work properly so far is to build my entire select statment as a string and then execute the string. This however is not an option because in the stored procedure, I am putting the result into a temp #table. And then later in the procedure, I am referrencing this temp #table. But since it wasn&#039t executed it doesn&#039t see that the table exists. Can anyone help? Please. Thanks. The procedure is below.<BR><BR><BR>CREATE PROCEDURE sp_respana (@begindte datetime, @enddte datetime, @scrtot varchar(100)) AS <BR><BR>select entrydate, scrcode, callno <BR> into #call1 <BR> from call <BR> where entrydate between @begindte AND @enddte <BR> and scrcode in (@scrtot)

  2. #2
    Vivek Nama Guest

    Default Help with Stored Procedure

    a dirty way out is to use a character other than &#039,&#039(comma) e.g. &#039@&#039 as delimiters to the items passed in the @scrtot parameter of the stored procedure<BR>Hence the "ATC","GEN" becomes "ATC@GEN"<BR>then inside the proc parse it and concatenate to make a valid SELECT .... IN (list value) clause

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •