store procedure

Question

Post reply

store procedure

ferrarielly

Valued Member

Points: 50
More actions
July 28, 2011 at 7:41 am

#254478

Hi,
I have this store procedure..the problem is that the result is for all fields NULL
What is wrong?
alter PROCEDURE [dbo].[LITABVERIFICAGIACENZA_INSERT] (@Codarticolo VarChar(50),@codlotto VarChar(50)OUTPUT,@giacenza decimal(38,6) OUTPUT, @datamodificalotto datetime output) AS
CREATE TABLE #listalotti (
[CODARTICOLO] [VARCHAR] (50) ,
[CODLOTTO] [VARCHAR] (15) ,
[GIACENZA] [DECIMAL](38,6) ,
[DATAMODIFICALOTTO] [datetime] ,
)
BEGIN
INSERT INTO #listalotti ( Codarticolo, codlotto, giacenza, datamodificalotto)
SELECT @Codarticolo, @codlotto, @giacenza, @datamodificalotto
FROM ANAGRAFICALOTTI INNER JOIN VISTAGIACENZEPARTITE ON VISTAGIACENZEPARTITE.CODARTICOLO = ANAGRAFICALOTTI.CODARTICOLO
WHERE ANAGRAFICALOTTI.CODARTICOLO=@Codarticolo
GROUP BY ANAGRAFICALOTTI.DATAMODIFICA
ORDER BY ANAGRAFICALOTTI.DATAMODIFICA
END;

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

{edit} sorry, wrong post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

You have 4 parameters inbound (3 of them are output). The body of your procedure doesn't actually do anything. You create a temp table and populate it with the values you passed in. Why bother with a temp table that is nothing more than the values you passed in? Even if this is used in another process it does not provide any benefit because the calling application/procedure already has all the information that was passed in.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ferrarielly Valued Member Points: 50 More actions · Answer 3

what you suggest? To create a "real" tale?

How can I check if my value are really written in my table?

I need to check the generated recordset

Thanx

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 4

ferrarielly (7/28/2011)
Hi,
I have this store procedure..the problem is that the result is for all fields NULL
What is wrong?
alter PROCEDURE [dbo].[LITABVERIFICAGIACENZA_INSERT] (@Codarticolo VarChar(50),@codlotto VarChar(50)OUTPUT,@giacenza decimal(38,6) OUTPUT, @datamodificalotto datetime output) AS
CREATE TABLE #listalotti (
[CODARTICOLO] [VARCHAR] (50) ,
[CODLOTTO] [VARCHAR] (15) ,
[GIACENZA] [DECIMAL](38,6) ,
[DATAMODIFICALOTTO] [datetime] ,
)
BEGIN
INSERT INTO #listalotti ( Codarticolo, codlotto, giacenza, datamodificalotto)
SELECT @Codarticolo, @codlotto, @giacenza, @datamodificalotto
FROM ANAGRAFICALOTTI INNER JOIN VISTAGIACENZEPARTITE ON VISTAGIACENZEPARTITE.CODARTICOLO = ANAGRAFICALOTTI.CODARTICOLO
WHERE ANAGRAFICALOTTI.CODARTICOLO=@Codarticolo
GROUP BY ANAGRAFICALOTTI.DATAMODIFICA
ORDER BY ANAGRAFICALOTTI.DATAMODIFICA
END;

I think what's being described is this:

You aren't selecting from the temp table, you are inserting into it and then doing nothing. Your query:

INSERT INTO #listalotti ( Codarticolo, codlotto, giacenza, datamodificalotto)

SELECT @Codarticolo, @codlotto, @giacenza, @datamodificalotto

FROM ANAGRAFICALOTTI INNER JOIN VISTAGIACENZEPARTITE ON VISTAGIACENZEPARTITE.CODARTICOLO = ANAGRAFICALOTTI.CODARTICOLO

WHERE ANAGRAFICALOTTI.CODARTICOLO=@Codarticolo

GROUP BY ANAGRAFICALOTTI.DATAMODIFICA

ORDER BY ANAGRAFICALOTTI.DATAMODIFICA

will not insert anything if the join/group criteria returns no rows. There is no way for us to know what you are trying to do with this. You take 4 input/output parameters and then insert into a temp table based on a join that is not defined by table schemas/sample data. You could insert 1 record or 1 million records and there is no way to know which. For what you are trying to describe might be this:

INSERT INTO #listalotti ( Codarticolo, codlotto, giacenza, datamodificalotto)

SELECT @Codarticolo, @codlotto, @giacenza, @datamodificalotto

SELECT * FROM #listalotti

Are you trying to return values in the OUTPUT Parameters? If so you need to do a select to get those values, maybe something like this:

SELECT

@Codarticolo = [SomeField1],

@codlotto = [SomeField2],

@giacenza = [SomeField3],

@datamodificalotto = [SomeField4]

FROM

dbo.[SomeTable]

WHERE

[SomeCriteria] = {some value}

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

be careful with this. For this to provide any useful information you need to make sure this select will return ONLY 1 row.

SELECT
@Codarticolo = [SomeField1],
@codlotto = [SomeField2],
@giacenza = [SomeField3],
@datamodificalotto = [SomeField4]
FROM
dbo.[SomeTable]
WHERE
[SomeCriteria] = {some value}

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

ferrarielly (7/28/2011)
what you suggest? To create a "real" tale?
How can I check if my value are really written in my table?
I need to check the generated recordset
Thanx

Maybe if you explain what you are trying to do we can help. Keep in mind that with the way this is coded your "recordset" is going to contain only the values you passed in. You will have a whole collection of identical records for each row in the query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ferrarielly Valued Member Points: 50 More actions · Answer 7

what I need to do is:

receiving a codart from an external code..I need to do a select in 2 different table extracting the rows related to this specific codart..

The result may be 1 or N rows...

Then I need to write this rows in a table (temp or real table..)

I need to do that..

Have you an example to do so?

ferrarielly Valued Member Points: 50 More actions · Answer 8

ferrarielly (7/28/2011)
what I need to do is:
receiving a codart from an external code..I need to do a select in 2 different table extracting the rows related to this specific codart..
The result may be 1 or N rows...
Then I need to write this rows in a table (temp or real table..)
I need to do that..
Have you an example to do so?

Even if you have an exaple that exctract value from a sinlgle table...IS FINE!!!!!!!!!!!!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

You want to receive a parameter and retrieve data from a current table based on what they passed in?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ferrarielly Valued Member Points: 50 More actions · Answer 10

yes correct...

I need to pass the codart value and retrive all rows related to this article saving them on a table

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Here is a generic template.

create procedure blah

(

@parameter1 int,

@parameter2 int

) as

begin

select MyCols from Table1

join Table2 on Table1.ID = Table2.ID

where MyVal1 = @parameter1 and MyVal2 = @parameter2

end

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

If you need to save this to a table just change the select to an insert.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/