March 9, 2011 at 10:05 am
I am creating stored procedure with this code:--
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[insert_kit_components]
as
INSERT INTO dbname.dbo.IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)
IF output inserted.ITEMNMBR ='NULL'
BEGIN
PRINT 'No items inserted'
Else
output inserted.ITEMNMBR
end
SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0
FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
(RIGHT(LTRIM(RTRIM(IM.ITEMNMBR)), LEN(LTRIM(RTRIM(IM.ITEMNMBR))) -
(CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR)))))) = UL.UOFM)
WHERE
IM.ITEMNMBR != '_' AND
IM.ITEMTYPE = 3 AND KT.ITEMNMBR IS NULL
ORDER BY
IM.ITEMNMBR
--------
i AM getting error as
Incorrect syntax near the keyword 'IF'.
Msg 4145, Level 15, State 1, Procedure insert_kit_components, Line 10
An expression of non-boolean type specified in a context where a condition is expected, near 'inserted'.
Msg 156, Level 15, State 1, Procedure insert_kit_components, Line 13
Incorrect syntax near the keyword 'Else'.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 9, 2011 at 10:37 am
couple of items:
1) The insert statement is incomplete....what are you trying to insert?
2) What is IF output inserted.ITEMNMBR ='NULL' ??
3) Is the field ITEMNMBR a null or does it have the string 'NULL'?
4) Are you trying to return a recordset after the insert? That's what will happen once you fix the INSERT statement.
to see if a field is null, you need to use the IS operator (i.e.):
IF ITEMNMBR IS NULL
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 9, 2011 at 10:50 am
ChazMan (3/9/2011)
couple of items:1) The insert statement is incomplete....what are you trying to insert?
2) What is IF output inserted.ITEMNMBR ='NULL' ??
3) Is the field ITEMNMBR a null or does it have the string 'NULL'?
4) Are you trying to return a recordset after the insert? That's what will happen once you fix the INSERT statement.
to see if a field is null, you need to use the IS operator (i.e.):
IF ITEMNMBR IS NULL
I am selecting data ( select, from ,where statements) and then tryin to insert them in the table.
I want to know whcih items got inserted in hte table and if there is no items inserted in the table so i should get 'no items inserted' otherwise i should get the output ( the items inserted)
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 9, 2011 at 10:55 am
you need to do the following structure:
DECLARE @TableVariable Table
INSERT INTO
(Field list)
OUTPUT INTO @TableVariable...
SELECT Statement
Then you have the @TableVariable that would contain what you inserted.
Lookup INSERT and OUTPUT in Books Online
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 9, 2011 at 11:02 am
First, Inserts don't work that way, with procedural code between the Insert statement and the data to be inserted (the Select statement in this case).
Second, "output" doesn't work that way. It can either output values into a recordset returned to the calling application, or it can output records into a table ("permanent", temporary, global temporary, or variable). It can't be used as if it were an object in some OOP language (which is how you're trying to use it here).
Third, Order By is almost always meaningless in an Insert Select statement, because the data will be stored based on the clustered index (unless you're forcing the order of an Identity column).
Fourth, are you going to always be running this stored procedure manually from Management Studio, or will it be called by an application or website (or a data access layer service)?
Fifth, Nulls don't work that way. You can't compare an empty recordset (nothing inserted) to a string with the value "NULL" and get anything useful out of it.
What you have here is something that would work (sort of) if T-SQL were an OOP language with some procedural components. It isn't.
This is probably what you actually want:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[insert_kit_components]
as
INSERT INTO dbname.dbo.IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)
output inserted.ITEMNMBR
SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0
FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
(RIGHT(LTRIM(RTRIM(IM.ITEMNMBR)), LEN(LTRIM(RTRIM(IM.ITEMNMBR))) -
(CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR)))))) = UL.UOFM)
WHERE
IM.ITEMNMBR != '_' AND
IM.ITEMTYPE = 3 AND KT.ITEMNMBR IS NULL;
IF @@rowcount = 0 -- nothing inserted
PRINT 'Nothing inserted';
You'll still get a dataset from the Output statement, but it will be empty if nothing is inserted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 11:58 am
GSquared (3/9/2011)
First, Inserts don't work that way, with procedural code between the Insert statement and the data to be inserted (the Select statement in this case).Second, "output" doesn't work that way. It can either output values into a recordset returned to the calling application, or it can output records into a table ("permanent", temporary, global temporary, or variable). It can't be used as if it were an object in some OOP language (which is how you're trying to use it here).
Third, Order By is almost always meaningless in an Insert Select statement, because the data will be stored based on the clustered index (unless you're forcing the order of an Identity column).
Fourth, are you going to always be running this stored procedure manually from Management Studio, or will it be called by an application or website (or a data access layer service)?
Fifth, Nulls don't work that way. You can't compare an empty recordset (nothing inserted) to a string with the value "NULL" and get anything useful out of it.
What you have here is something that would work (sort of) if T-SQL were an OOP language with some procedural components. It isn't.
This is probably what you actually want:
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[insert_kit_components]
as
INSERT INTO dbname.dbo.IV00104
(ITEMNMBR,
SEQNUMBR,
CMPTITNM,
CMPITUOM,
CMPITQTY,
CMPSERNM)
output inserted.ITEMNMBR
SELECT
IM.ITEMNMBR,
16384,
LEFT(LTRIM(RTRIM(IM.ITEMNMBR)), (CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR))) - 1)),
UL.UOFM,
1.00000,
0
FROM
IV00101 IM LEFT JOIN IV00104 KT ON IM.ITEMNMBR = KT.ITEMNMBR
JOIN IV40202 UL ON (IM.UOMSCHDL = UL.UOMSCHDL AND
(RIGHT(LTRIM(RTRIM(IM.ITEMNMBR)), LEN(LTRIM(RTRIM(IM.ITEMNMBR))) -
(CHARINDEX('-', LTRIM(RTRIM(IM.ITEMNMBR)))))) = UL.UOFM)
WHERE
IM.ITEMNMBR != '_' AND
IM.ITEMTYPE = 3 AND KT.ITEMNMBR IS NULL;
IF @@rowcount = 0 -- nothing inserted
PRINT 'Nothing inserted';
You'll still get a dataset from the Output statement, but it will be empty if nothing is inserted.
Ohk, that means i added OOPS concepts with t sql...huh...its confusing..:(
I will be calling this proc from another proc in a job ...
I created the proc as u suggested, but when there was no items inserted, it didnt print 'nothing inserted', it was just blank output wid the column name..
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 9, 2011 at 12:03 pm
You will need to pass the output to a table variable. Otherwise the procedure is going to return the contents of the Output operation.
then you can do something like:
if exists(select 1 from @TableVariable)
begin
Select * from @TableVariable
end
else
begin
Print 'No Records Inserted'
end
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 9, 2011 at 12:13 pm
The "print" command is really only useful if you're calling the procedure from Management Studio. It doesn't really do much if you call the procedure from an application or web page.
You can use Raiserror to get an error message to go to the app. Alternately, in the app itself, it can detect that there are no records in the output recordset, and handle it there (that would be more usual).
Within Management Studio, if you run it as I wrote it, it will return an empty recordset to the main output screen, and will write the no records on the Messages tab. That won't help much in the application, but it's probably why you're not seeing the message, because that tab is behind the datagrid tab.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 12:15 pm
i defer and agree with gsquared.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 9, 2011 at 12:27 pm
I am calling that procedure in this procedure...
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Sushant Yadav',
@recipients='syadav@xyz.com',
@subject = 'Insert Kit Components',
@body_format = 'HTML',
@query = 'exec dbo.insert_kit_components',
@execute_query_database = 'dbname',
@query_result_header = 1,
@exclude_query_output = 1,
@query_result_width = 1000,
@append_query_error = 1,
@attach_query_result_as_file = 0,
@query_result_separator = '~',
@query_result_no_padding = 0
So, when i run this, i get a mail...
when there are no items inserted ..i get the mail body as--
ITEMNMBR ------------------------------- (0 rows affected) No Items Inserted
IS it possible to just get 'No items inserted' ??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 9, 2011 at 12:30 pm
At the beginning of the proc, after "AS", add "SET NOCOUNT ON".
create procedure [dbo].[insert_kit_components]
as
SET NOCOUNT ON;
INSERT INTO dbname.dbo.IV00104
That should get rid of that part.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 12:37 pm
Now I got the mail body as :-
ITEMNMBR ------------------------------- No items Inserted
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 9, 2011 at 12:50 pm
SKYBVI (3/9/2011)
Now I got the mail body as :-ITEMNMBR ------------------------------- No items Inserted
Regards,
Sushant
Is that what you want? It seems to be, from the prior post, but I'm not certain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 12:55 pm
I meant the mail body should look like --
No Items Inserted.
IF its possible, otherwise also its fine.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
March 10, 2011 at 6:51 am
You'd have to rework the way you're calling the data in order to get that to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply