Stored procedure problem 1

aspvbnetnerd · Apr 11, 2007

I have a procedure like this.

Code:

[COLOR=blue]CREATE PROCEDURE[/color]	usp_GetSales
					@DATEFROM		[COLOR=blue]AS VARCHAR(10)[/color],
					@DATETO		  [COLOR=blue]AS VARCHAR(10)[/color],
					@ID_TYPE		 [COLOR=blue]AS VARCHAR(10)[/color]
AS
DECLARE @Sales TABLE(DATUM [COLOR=blue]VARCHAR[/color](10), TIDPUNKT [COLOR=blue]VARCHAR[/color](8), KASSOR [COLOR=blue]INTEGER[/color], KASSA [COLOR=blue]INTEGER[/color], FORS_BRUTTO [COLOR=blue]DECIMAL[/color](12,2), [COLOR=blue]MOMS DECIMAL(12,2)[/color], KUNDER [COLOR=blue]INTEGER[/color], POSTER [COLOR=blue]INTEGER PRIMARY KEY[/color](DATUM, TIDPUNKT, KASSOR, KASSA))	

[COLOR=blue]INSERT INTO[/color] @Sales [COLOR=blue]SELECT[/color] DATUM, TIDPUNKT, KASSOR, KASSA, [COLOR=#FF00FF]SUM[/color](FORS_BRUTTO), [COLOR=#FF00FF]SUM[/color](MOMS), [COLOR=#FF00FF]SUM[/color](KUNDER), [COLOR=#FF00FF]SUM[/color](POSTER) [COLOR=blue]FROM[/color] FREKVENS_GRUPP [COLOR=blue]WHERE[/color] DATUM >= @DATEFROM AND DATUM <= @DATETO AND ID_TYP IN (1, 2) [COLOR=blue]GROUP BY[/color] DATUM, TIDPUNKT, KASSOR, KASSA

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Sales

If execute it like this the it is okay

Code:

usp_GetSales '2006-09-03', '2006-09-03', '1'

But i should also be able to execute it like this.
Sometimes I want to be able to send these parameters to the stored procedure, depending what the user choses '1,2' but when I send it like this i get an error like this
Conversion failed when converting the varchar value '1,2' to data type int.

Code:

usp_GetSales '2006-09-03', '2006-09-03', '1,2'

The column ID_TYP from table Frekvens_grupp is an INT

Hope you understand my problem

George

Crowley16 · Apr 11, 2007

you will need to create a dynamic sql statement and build it with the passed in @ID_TYPE parameter

--------------------
Procrastinate Now!

onpnt · Apr 11, 2007

I had he same thing awhile ago and used a UDF to split the parm so I could put it in the IN clause.

I believe there was a thread just recently that gave an examle of split functionality in SQL. Try searching for it in this forum

_{____________ signature below ______________

The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

aspvbnetnerd · Apr 11, 2007

Thanks for the response,

onpnt, I tried to view on all your response on sql could not find the split thread you had.

aspvbnetnerd · Apr 11, 2007

Crowley16, did you mean something like this.

Code:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color]	usp_GetSales
					@DATEFROM		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
					@DATETO			[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
					@ID_TYPE		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10)
[COLOR=blue]AS[/color]
[COLOR=blue]DECLARE[/color] @Sales [COLOR=blue]TABLE[/color](DATUM [COLOR=blue]VARCHAR[/color](10), TIDPUNKT [COLOR=blue]VARCHAR[/color](8), KASSOR [COLOR=blue]INTEGER[/color], KASSA [COLOR=blue]INTEGER[/color], FORS_BRUTTO [COLOR=blue]DECIMAL[/color](12,2), MOMS [COLOR=blue]DECIMAL[/color](12,2), KUNDER [COLOR=blue]INTEGER[/color], POSTER [COLOR=blue]INTEGER[/color] [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color](DATUM, TIDPUNKT, KASSOR, KASSA))	
[COLOR=blue]DECLARE[/color] @SQL	[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](1000)

[COLOR=blue]SET[/color] @SQL = [COLOR=red]'INSERT INTO @Sales SELECT DATUM, TIDPUNKT, KASSOR, KASSA, SUM(FORS_BRUTTO), SUM(MOMS), SUM(KUNDER), SUM(POSTER) FROM FREKVENS_GRUPP WHERE DATUM >= '[/color] + @DATEFROM + [COLOR=red]'AND DATUM <= '[/color] + @DATETO + [COLOR=red]'AND ID_TYP IN ('[/color] + @ID_TYPE + [COLOR=red]') GROUP BY DATUM, TIDPUNKT, KASSOR, KASSA'[/color]

[COLOR=blue]EXEC[/color](@SQL)

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Sales

I tried something like this and got this message
Must declare the table variable "@Sales".

George

robinCQ · Apr 11, 2007

That's because it's attempting to parse @Sales from within the scope of the EXEC(@SQL) command, but it hasn't been defined in there.

You'll need to concatenate it like you did with @DATEFROM, @DATETO, etc.

Incidentally, this happens the other way round too -- if you define and set a variable from within an EXEC() command, you won't be able to access it from the calling code.

robinCQ · Apr 11, 2007

Ignore the middle paragraph of my last post sorry, I failed to realise your @Sales is a table variable. Looks like it's time I went for lunch!

Crowley16 · Apr 11, 2007

you're right robin, that's exactly the case...

the dynamic sql string is treated as a completely different procedure which lives within it's own scope.

what I'd do is use temp tables, ## to make them global...

--------------------
Procrastinate Now!

aspvbnetnerd · Apr 11, 2007

I tried this and i got this error when compiling the stored procedure

Code:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color]	usp_GetSales
					@DATEFROM		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
					@DATETO		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
					@ID_TYPE		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10)
[COLOR=blue]AS[/color]
[COLOR=blue]DECLARE[/color] @Sales		[COLOR=blue]TABLE[/color](DATUM [COLOR=blue]VARCHAR[/color](10), TIDPUNKT [COLOR=blue]VARCHAR[/color](8), KASSOR [COLOR=blue]INTEGER[/color], KASSA [COLOR=blue]INTEGER[/color], FORS_BRUTTO [COLOR=blue]DECIMAL[/color](12,2), MOMS [COLOR=blue]DECIMAL[/color](12,2), KUNDER [COLOR=blue]INTEGER[/color], POSTER [COLOR=blue]INTEGER[/color] [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color](DATUM, TIDPUNKT, KASSOR, KASSA))	
[COLOR=blue]DECLARE[/color] @SQL		[COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](1000)

[COLOR=blue]SET[/color] @SQL = [COLOR=red]'INSERT INTO '[/color] + @Sales + [COLOR=red]'SELECT DATUM, TIDPUNKT, KASSOR, KASSA, SUM(FORS_BRUTTO), SUM(MOMS), SUM(KUNDER), SUM(POSTER) FROM FREKVENS_GRUPP WHERE DATUM >= '[/color] + @DATEFROM + [COLOR=red]'AND DATUM <= '[/color] + @DATETO + [COLOR=red]'AND ID_TYP IN ('[/color] + @ID_TYPE + [COLOR=red]') GROUP BY DATUM, TIDPUNKT, KASSOR, KASSA'[/color]

[COLOR=blue]EXEC[/color](@SQL)

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Sales

Must declare the scalar variable "@Sales"

George

gmmastros · Apr 11, 2007

First, create this User Defined Function:

Code:

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] Split(@CommaDelimitedFieldNames [COLOR=blue]Varchar[/color](8000),@SplitChar [COLOR=blue]VarChar[/color](10))  
Returns @Tbl_FieldNames [COLOR=blue]Table[/color]  (FieldName [COLOR=blue]VarChar[/color](8000) [COLOR=blue]primary[/color] [COLOR=blue]key[/color])  [COLOR=blue]As[/color]  

[COLOR=blue]Begin[/color] 
 [COLOR=blue]Set[/color] @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

 [COLOR=blue]Declare[/color] @Pos1 [COLOR=blue]Int[/color]
 [COLOR=blue]Declare[/color] @pos2 [COLOR=blue]Int[/color]
 
 [COLOR=blue]Set[/color] @Pos1=1
 [COLOR=blue]Set[/color] @Pos2=1

 [COLOR=blue]While[/color] @Pos1<Len(@CommaDelimitedFieldNames)
 [COLOR=blue]Begin[/color]
  [COLOR=blue]Set[/color] @Pos1 = [COLOR=#FF00FF]CharIndex[/color](@SplitChar,@CommaDelimitedFieldNames,@Pos1)
  [COLOR=blue]Insert[/color] @Tbl_FieldNames [COLOR=blue]Select[/color]  [COLOR=#FF00FF]Cast[/color]([COLOR=#FF00FF]Substring[/color](@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) [COLOR=blue]As[/color] [COLOR=blue]VarChar[/color](8000))
  [COLOR=blue]Set[/color] @Pos2=@Pos1+1
  [COLOR=blue]Set[/color] @Pos1 = @Pos1+1
 [COLOR=blue]End[/color] 
 [COLOR=blue]Return[/color]
[COLOR=blue]End[/color]

Then, you can use it like this...

Code:

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color]    usp_GetSales
                    @DATEFROM        [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
                    @DATETO          [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10),
                    @ID_TYPE         [COLOR=blue]AS[/color] [COLOR=blue]VARCHAR[/color](10)
[COLOR=blue]AS[/color]
[COLOR=blue]DECLARE[/color] @Sales 
[COLOR=blue]TABLE[/color]   (DATUM [COLOR=blue]VARCHAR[/color](10), 
        TIDPUNKT [COLOR=blue]VARCHAR[/color](8), 
        KASSOR [COLOR=blue]INTEGER[/color], 
        KASSA [COLOR=blue]INTEGER[/color], 
        FORS_BRUTTO [COLOR=blue]DECIMAL[/color](12,2), 
        MOMS [COLOR=blue]DECIMAL[/color](12,2), 
        KUNDER [COLOR=blue]INTEGER[/color], 
        POSTER [COLOR=blue]INTEGER[/color] 
        [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color](DATUM, TIDPUNKT, KASSOR, KASSA))    

[COLOR=blue]Declare[/color] @Ids
[COLOR=blue]Table[/color]   (ID_TYP [COLOR=blue]Int[/color])

[COLOR=blue]Insert[/color] 
[COLOR=blue]Into[/color]    @Ids(ID_TYP)
[COLOR=blue]Select[/color]  [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Int[/color], FieldName)
[COLOR=blue]From[/color]    dbo.FieldNameSplitter(@ID_TYPE)

[COLOR=blue]INSERT[/color] 
[COLOR=blue]INTO[/color]    @Sales 
[COLOR=blue]SELECT[/color]  DATUM, 
        TIDPUNKT, 
        KASSOR, 
        KASSA, 
        SUM(FORS_BRUTTO), 
        SUM(MOMS), 
        SUM(KUNDER), 
        SUM(POSTER) 
[COLOR=blue]FROM[/color]    FREKVENS_GRUPP 
        [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Ids I
          [COLOR=blue]On[/color] FREKVENS_GRUPP.ID_TYP = I.ID_TYP
[COLOR=blue]WHERE[/color]   DATUM >= @DATEFROM 
        AND DATUM <= @DATETO 
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] DATUM, TIDPUNKT, KASSOR, KASSA

[COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] @Sales

Notice that I create a table variable to hold your ids's. Each comma delimited id will end up as a row in the @Ids table variable. So, to limit the results, you simply inner join on this table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

aspvbnetnerd · Apr 11, 2007

gmmastros, thanks for the help.
The function Split was way off my knowledge.
But when i execute the storedprocedure like this

Code:

usp_GetSales [COLOR=red]'2006-09-03'[/color], [COLOR=red]'2006-09-03'[/color], [COLOR=red]'1,2'[/color]

I recieve this error
Invalid object name 'dbo.FieldNameSplitter'

George

gmmastros · Apr 11, 2007

Sorry...

Change:
From dbo.FieldNameSplitter(@ID_TYPE)

To:
From dbo.Split(@ID_TYPE, ',')

I have a couple of these functions floating around. [blush]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Stored procedure problem 1

aspvbnetnerd

Programmer

Crowley16

Technical User

onpnt

Programmer

aspvbnetnerd

Programmer

aspvbnetnerd

Programmer

robinCQ

Programmer

robinCQ

Programmer

Crowley16

Technical User

aspvbnetnerd

Programmer

gmmastros

Programmer

aspvbnetnerd

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor