Why this store procedure give me error,

Question

0.00/5 (No votes)

See more:

Here I am creating a store procedure for taking values from two different tables and inserting that values in third table.

SQL

CREATE PROCEDURE [dbo].[sp_payout]
    (
      @sponcorid varchar(50)=null
 
 )
AS
BEGIN
    INSERT INTO Payout_Master
SELECT sponcorid,leftcount,rightcount,totcount,pair FROM
((SELECT sponcorid,leftcount,rightcount,totcount,pair FROM TestCounter_Master WHERE sponcorid=@sponcorid
EXCEPT
SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)
UNION(SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master))
END
GO

ERROR:
Incorrect syntax near the keyword 'END'.

Help me out please

Posted 3-Feb-13 4:13am

sumit kausalye

Updated 3-Feb-13 17:56pm

Amir Mahfoozi

v4

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Answer 1 · 2013-02-03T17:53:00

Solution 3

Just give some alias name at the end of below line.

SQL

UNION(SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)) mytable

updated sp

SQL

CREATE PROCEDURE [dbo].[sp_payout]
(
@sponcorid varchar(50)=null

)
AS
BEGIN
    INSERT INTO Payout_Master
        SELECT sponcorid,leftcount,rightcount,totcount,pair FROM
((SELECT sponcorid,leftcount,rightcount,totcount,pair FROM TestCounter_Master WHERE sponcorid=@sponcorid
EXCEPT
SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)
UNION(SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master)) mytable
END
GO

Posted 3-Feb-13 17:53pm

Santhosh Kumar Jayaraman

Comments

Amir Mahfoozi 3-Feb-13 23:58pm

+5

sumit kausalye 4-Feb-13 0:07am

If i apply this code it gives me this error:
ERROR:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Santhosh Kumar Jayaraman 4-Feb-13 0:09am

I believe you didnt get compiler error. It would be a run time error which is different than what you had earlier.
yes thats right.Thats because you have 5 columns in the first select statement and only 4 columns in the except and Union statements, you are missing one column

sumit kausalye 4-Feb-13 11:38am

Its A complier error

sumit kausalye 4-Feb-13 11:40am

And i am not match the two queries, i just want records from these two tables that is testcounter_Master and SetPayout_Master and insert that values into new table that is payout_Master

milenalukic · Answer 2 · 2013-02-03T06:40:00

Solution 1

Hi,

It looks like you are missing some of the syntax for a stored procedure.

SQL

CREATE PROCEDURE <procedure_name,> 
	-- Add the parameters for the stored procedure here

AS
BEGIN
        -- Insert statements for procedure here
END
GO

So after AS you need to insert BEGIN and END at the end.

The insert statement itself looks good.

Posted 3-Feb-13 6:40am

milenalukic

Comments

sumit kausalye 3-Feb-13 23:20pm

still it give me same error

Sandeep Mewara · Answer 3 · 2013-02-03T06:42:00

You cannot instantiate a parameter while defining.

Do:

SQL

CREATE PROCEDURE [dbo].[sp_payout]
     (
        @sponcorid varchar(50)
     )
 AS
   INSERT INTO 
       Payout_Master
   SELECT 
       sponcorid,leftcount,rightcount,totcount,pair 
   FROM
       (
         (
          SELECT sponcorid,leftcount,rightcount,totcount,pair FROM TestCounter_Master WHERE sponcorid=@sponcorid
          EXCEPT
          SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master
         )
         UNION
         (
          SELECT tds,sercharge,deve_charges,trimming FROM SetPayout_Master
          )
       )