Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unhappy Unanswered: Stored Procedure

    I want to have a stored procedure execute a query that returns multiple rows then I want the stored procedure to take the result set of that query and use it as the parameter for another stored procedure. The parameter can be just text, I do not necessarily need row separation. It will be the body of an email message. I hope this makes sense.

    Example:
    CREATE PROCEDURE check_review AS

    Declare @recs4rev varchar (500)
    SET @recs4rev = (select ID from Some_TableDesign where revdate < getdate() )

    EXEC stored_proc_4_email @From="name@email.com", @To="name2@email.com", @subject="The subject", @Body=@recs4rev
    Go

    I already know this doesn't work and I sort of played with cursors, but felt like I was not going in the right direction. I hope this makes sense, any help you can offer would be most appreciated. Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    454

    try this

    CREATE PROCEDURE check_review AS

    DECLARE @recs4rev varchar (500)
    DECLARE @ID_temp int -- if ID column is set to int
    DECLARE ID_Cursor CURSOR
    FOR
    SELECT ID FROM Some_TableDesign WHERE revdate < getdate()
    OPEN ID_Cursor
    FETCH NEXT FROM ID_Cursor INTO @ID_temp
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @recs4rev = @recs4rev + @ID_temp + ', '

    FETCH NEXT FROM ID_Cursor INTO @ID_temp
    END

    CLOSE ID_Cursor
    DEALLOCATE ID_Cursor

    EXEC stored_proc_4_email @From="name@email.com", @To="name2@email.com", @subject="The subject", @Body=@recs4rev
    Go

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    I will try that, I see that I did not use the fetch status in my earlier attempts! Thanks!!!!

  4. #4
    Join Date
    Jan 2004
    Posts
    6
    It appears that the SET command is not functioning. The query is running, I can print the results of the cursors @ID_TEMP variable, but it will not set the @recs4rev variable. The Print @recs4rev + "RECS4REV" is blank. The @@FetchStatus is 0

    Here is what I have so far:
    CREATE PROCEDURE sp_check_review AS

    DECLARE @recs4rev varchar (500)
    DECLARE @ID_temp varchar(50)
    DECLARE ID_Cursor CURSOR

    FOR
    SELECT ID FROM Some_TableDesign WHERE revdate < getdate() -30
    OPEN ID_Cursor
    FETCH NEXT FROM ID_Cursor INTO @ID_temp
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @recs4rev = @recs4rev + @ID_temp + ", "
    PRINT @ID_temp + "IDTEMP"
    Print @recs4rev + "RECS4REV"
    PRINT @@FETCH_STATUS
    FETCH NEXT FROM ID_Cursor INTO @ID_temp

    END
    CLOSE ID_Cursor
    DEALLOCATE ID_Cursor

    EXEC sp_send_cdosysmail @From="moron@email.com", @To="moron@email.com", @subject="The subject", @Body=@recs4rev
    GO



    Sample results:

    3754IDTEMP

    0
    3782IDTEMP

    0
    3783IDTEMP

    0

    Notice the big blank in between IDTEMP and the 0. There is no recs4rev! HELP!!!

  5. #5
    Join Date
    Dec 2003
    Posts
    454

    data type of ID

    What is the data type of the column ID? If it is int, you need to use "DECLARE @ID_temp int" instead of "DECLARE @ID_temp varchar(50)".

  6. #6
    Join Date
    Jan 2004
    Posts
    6
    It is an integer. My bad. However, the problem still sort of persists. I received a message (this is why I switched it to varchar w/o thinking. I assumed ID was varchar, duh!) like this:

    Server: Msg 245, Level 16, State 1, Procedure sp_check_review, Line 13
    Syntax error converting the varchar value ', ' to a column of data type int.

    So I tried using the convert funtion on the query itself:
    SELECT CONVERT(char(50), ID) FROM Some_TableDesign WHERE reviewdate < getdate() -30
    That gave me this result:
    3754IDTEMP

    0
    3782IDTEMP

    0
    3783IDTEMP

    0

    Which is the same thing as before. I have also tried retrieving from a varchar column and the result is the same (not doing the convert mumbo jumbo and setting the select statement to do a regular varchar column). It still seems like the variable is not receiving the information.
    Ideas?
    And by the way: THANK YOU!

  7. #7
    Join Date
    Jan 2004
    Posts
    6
    More information - It is definitely when the statement below is executed:

    SET @recs4rev = @recs4rev + @id_temp +","

    If I remove the @recs4rev it works, The variable gets set and the information is sent. But it only sends me the last record in the cursor (obviously). So how do I get it to add on? I need that variable to build the string with all the ID numbers.

    Thoughts?

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    Try this

    SET @recs4rev = @recs4rev + CAST(@ID_temp AS varchar) +", "

    or

    SET @recs4rev = @recs4rev + CONVERT (varchar(50), @ID_temp) +", "

    and keep @ID_temp as int data type

  9. #9
    Join Date
    Jan 2004
    Posts
    6

    Smile

    OK, I think I got it.
    I had to set the recs4rev variable to an empty string before the while loop. Then it worked. I know that variables upon declaration are null until set. So perhaps this was throwing it of...see Null + @id_temp +"," was proably just null to the server. Dunno, but set @recs4rev =" " just below the open cursor command seems to have done the trick,
    THANK YOU SO MUCH FOR YOUR HELP!!!!

  10. #10
    Join Date
    Dec 2003
    Posts
    454
    Originally posted by Jdemon
    OK, I think I got it.
    I had to set the recs4rev variable to an empty string before the while loop. Then it worked. I know that variables upon declaration are null until set. So perhaps this was throwing it of...see Null + @id_temp +"," was proably just null to the server. Dunno, but set @recs4rev =" " just below the open cursor command seems to have done the trick,
    THANK YOU SO MUCH FOR YOUR HELP!!!!
    You are right. I forgot to initiliaze the variable @recs4rev. Cheers.

Posting Permissions

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