September 25, 2015 at 8:35 am
Hi All.
I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
SELECT M.ID, C.ID, CM.FULL_NAME
FROM ServiceLine_Committee.DBO.tblCommitteeName C, ServiceLine_Committee.DBO.tblMeetings M, ServiceLine_Committee.DBO.tblCommitteeMembers CM
WHERE (M.ContractCategory= C.Contract_Category
AND M.Committee = C.Committee_Name
AND CM.Committee= M.Committee)
END
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement. Can someone help me please so that I cannot pass the values? Thanks.
September 25, 2015 at 8:50 am
deedv (9/25/2015)
Hi All.I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
SELECT M.ID, C.ID, CM.FULL_NAME
FROM ServiceLine_Committee.DBO.tblCommitteeName C, ServiceLine_Committee.DBO.tblMeetings M, ServiceLine_Committee.DBO.tblCommitteeMembers CM
WHERE (M.ContractCategory= C.Contract_Category
AND M.Committee = C.Committee_Name
AND CM.Committee= M.Committee)
END
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement. Can someone help me please so that I cannot pass the values? Thanks.
Hi and welcome to the forums. It is not clear what you are trying to do here. What are the variables? You seem to have some naming conventions which are less than optimal. I am guessing the MID is MeetingID? And CID is CommitteeID? You would help yourself a lot in the long run if you use meaningful names instead of really short cryptic abbreviations. Also it is not a great idea to use prefixes on object names. And the sp_ prefix is a particularly poor choice. That is reserved for system procedures from MS. On top of possible name collisions you can actually hinder performance with that prefix. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Then you have your query where you are using the old style joins. Those have been out of favor for nearly 30 years now in favor of explicit joins. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx
I don't understand why these variables are defined as output? And when you use nvarchar you ALWAYS need to specify the size. Otherwise you get the default and that can vary depending on usage.
If I were going to make a guess at what you want it would be something like this.
ALTER PROCEDURE [dbo].[sp_insert_test]
(
@MID INT,
@CID INT,
@Member NVARCHAR(50) --You ALWAYS need to specify the length of the variable
) as
set nocount on;
INSERT INTO ServiceLine_Committee.dbo.tblVotings
(
Meeting_ID
, Committee_ID
, Member_Name
)
VALUES
(
@MID
, @CID
, @Member
)
_______________________________________________________________
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/
September 25, 2015 at 9:07 am
Sean, I think he's wanting to insert the values into tblVoting, and at the same time return the inserted values as output parameters. Obviously that'll only work if that SELECT query returns a single row. The best way of doing that is with an output clause.
John
September 25, 2015 at 9:40 am
Kinda hard to understand what's wrong, but maybe start with this
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CID INT OUTPUT,
@Member NVARCHAR OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
SELECT M.ID, C.ID, CM.FULL_NAME
FROM ServiceLine_Committee.DBO.tblCommitteeName C
inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
where M.ID = @MID AND C.ID = @CID
END
If that's not what you want, please explain the problem in more detail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2015 at 9:55 am
John Mitchell-245523 (9/25/2015)
Sean, I think he's wanting to insert the values into tblVoting, and at the same time return the inserted values as output parameters. Obviously that'll only work if that SELECT query returns a single row. The best way of doing that is with an output clause.John
Could be. Of course using output parameters to retrieve the values you just passed in seems a bit silly. 😉 No if those values are changed inside the procedure that is another story but as posted the output seems to be adding complexity when it isn't needed.
_______________________________________________________________
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/
September 25, 2015 at 10:01 am
He didn't pass the parameters in, though - they're all output parameters. The values were got from the tables.
John
September 25, 2015 at 10:12 am
John Mitchell-245523 (9/25/2015)
He didn't pass the parameters in, though - they're all output parameters. The values were got from the tables.John
Well actually he didn't even reference them anywhere in the code which is what lead to the confusion in the first place. 😛
_______________________________________________________________
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/
September 25, 2015 at 10:19 am
Thank you. This is what I needed.
I ended up with this code where I only have to enter the Meeting ID to pass and then the other two columns were inserted automatically just based on the Meeting ID I entered.
@MID INT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ServiceLine_Committee.dbo.tblVotings (Meeting_ID, Committee_ID, Member_Name)
SELECT M.ID, C.ID, CM.FULL_NAME
FROM ServiceLine_Committee.DBO.tblCommitteeName C
inner join ServiceLine_Committee.DBO.tblMeetings M on M.ContractCategory = C.Contract_Category AND M.Committee = C.Committee_Name
inner join ServiceLine_Committee.DBO.tblCommitteeMembers CM on CM.Committee = M.Committee
where M.ID = @MID
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply