Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: Stored Procedure Parameter

    Please help me with my sproc. I have this code:

    Code:
    ALTER PROCEDURE [dbo].[Sales by CustSummary]
    	@code varchar(500),
    	@fromdate date,
    	@todate date	
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	SELECT [Sales].[TransDate],[Sales].[StoreCode],[Branch].[Description],
    	[Sales].[Product ID], [Sales].[Units],[Sales].[Price]
    	FROM [Sales],[ Branch]
    
    	WHERE 	[Sales].[StoreCode]=[Branch].[StoreCode]
    	and [Sales].[StoreCode] in (@code)
    	and [TransDate] between @fromdate and @todate
    END
    But then nothing is retrieved. I have read in an article that this is not the right way to pass @code with IN. But I don't know how to correct this.

    @code has multiple items thats why i cant use =(equals) or assign.

    I hope you can help me. Thnx!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    common problem. pick your solution...

    sql server comma separated values to table - Google Search
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In SQL 2008 you can pass a table parameter. This is a yummy way of doing it so let us know your version.
    Last edited by blindman; 11-06-09 at 14:46. Reason: Pootle cannot type or spell.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump View Post
    In SQL 2008 you can pass a table parameter. This is a yummy way of doing it so let us know your version.
    You had to edit THAT??????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2009
    Posts
    90
    This one is working:
    Code:
    DECLARE @code varchar(max),
    		@fromdate date,
    		@todate date,
    		@SQL varchar(2000)
    		
    SET @code='101,102'
    SET @fromdate='3/1/2009'
    SET @todate='3/1/2010'
    
    SET @SQL = 'SELECT [Sales].[TransDate],[Sales].[StoreCode],[Branch].[Description],
    	[Sales].[Product ID], [Sales].[Units],[Sales].[Price]
    	FROM [Sales],[Branch]
    
    	WHERE 	[Sales].[StoreCode]=[Branch].[StoreCode]
    	and [Sales].[StoreCode] in ('+@code+')'
    	
    	EXECUTE (@SQL)
    However, every time i add the where clause for the date variable like this one:
    Code:
    SET @SQL = 'SELECT [Sales].[TransDate],[Sales].[StoreCode],[Branch].[Description],
    	[Sales].[Product ID], [Sales].[Units],[Sales].[Price]
    	FROM [Sales],[Branch]
    
    	WHERE 	[Sales].[StoreCode]=[Branch].[StoreCode]
    	and [Sales].[StoreCode] in ('+@code+')
    	and [TransDate] between '+@fromdate+' and '+@todate+''
    	
    	EXECUTE (@SQL)
    I always get this error:
    Code:
    The data types varchar(max) and date are incompatible in the add operator.
    What's wrong with this code? I know that its on the date variable. I guess

  6. #6
    Join Date
    May 2009
    Posts
    13
    Since you're using dynamic SQL (and accepting risks that go along with it), the string you are concatenating together into @SQL should consist of string types: Datetime types need to be type-casted.

    Try: cast(@fromdate as varchar(20))

    Of course, do the same for @todate

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dynamic sql to avoid the IN thing....

    Don't think I've seen that before

    I think you REALLY want to and IMPLEMENT a solution like this

    Using a CSV with an IN sub-select - SQLTeam.com

    and THROW AWAY the dynamic SQL nonsense

    It's a hack
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2009
    Posts
    90
    Thanks for all your help!
    SQL Injection is not an issue for what I'm doing.
    But I'll be trying the other solution.
    Thnx!

  9. #9
    Join Date
    Sep 2009
    Posts
    90
    There's an error everytime i cast the date:

    Code:
    WHERE 	[Sales].[StoreCode]=[Branch].[StoreCode]
    	and [Sales].[StoreCode] in ('+@code+')
    	and [TransDate] between '+cast(@fromdate as varchar(20))+' and '+cast(@todate as varchar(20))+''
    Code:
    Operand type clash: date is incompatible with int
    What's wrong?

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Alter your code in:
    Code:
    ...
    	PRINT @SQL
    --	EXECUTE (@SQL)
    That way you will see what error you've made. If you don't immediately see what's wrong, copy-paste it and see what error message you get when you try to execute it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you still using dynamic SQL or not?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Sep 2009
    Posts
    90
    Quote Originally Posted by Wim View Post
    Alter your code in:
    Code:
    ...
    	PRINT @SQL
    --	EXECUTE (@SQL)
    That solved the date problem. Just have to put additional single quotes.

    @Brett Kaiser I've read the article Using a CSV with an IN sub-select - SQLTeam.com and it's great. I will try to use this method.
    What if I have two or three parameters with between dates?

    Thanks again!

  13. #13
    Join Date
    Sep 2009
    Posts
    90
    How will i alter the function if I have two parameters?

  14. #14
    Join Date
    Sep 2009
    Posts
    90
    I think I solved it. I just have to create two csv function and then join them like this:

    Code:
    select *
    from dbo.Sales 
    JOIN dbo.CsvToInt1(@code) CSV1 
    ON CSV1.IntValue = StoreCode
    JOIN dbo.CsvToInt2(@prodid) CSV2
    ON CSV2.IntValue = ProductID
    WHERE transdate betwee date1 and date2
    Is this right way?
    Last edited by rvr707; 11-11-09 at 02:46.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your joining to a list

    did you test it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Tags for this Thread

Posting Permissions

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