question

KShahzad-8722 avatar image
0 Votes"
KShahzad-8722 asked EchoLiu-msft commented

Create sql server stored procedure

I have a query to run as below and i want to run below as a stored procedure, how can i

SELECT *
INTO database.schema.table
FROM database.schema.table

delete from database.schem.table

please assist

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped.
Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Here is an example of a stored procedure and how to execute it:

CREATE PROCEDURE MySP @par1 int,
                      @par2 nvarchar(40) AS
   SELECT  col1, col2, col3
   FROM    sometable
   WHERE   keycol1 = @par1
     AND   keycol2 = @par2
go
EXEC MySP 36, 'xyz'

Beware that CREATE PROCEDURE must be a batch of its own. There can be no statements directly before CREATE PROCEDURE, but there must be a go separator first. And you also need a go at the end of the procedure to delimit from statements that follows.

(Keep in mind that go is not an SQL statement. That's an instruction to the query tool to split up the script in batches and send one batch at a time to SQL Server.)


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks,

i am looking for SP without parameter,

if you could please advise.

0 Votes 0 ·

I added parameters to the example, as I suspected that would be your next question.

If you want a stored procedure without parameters, well, don't enter any parameters. It's as simple as that.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ErlandSommarskog commented

Hi @KShahzad-8722

Welcome to microsoft TSQL Q&A forum!

Please try:

     create procedure a_stu
     as
     SELECT *
     INTO database.schema.table
     FROM database.schema.table
        
     --delete from database.schem.table
        
     execute a_stu 

The execute a_stu statement must be executed separately from other statements.


If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply,

are we not using braces around to start and end the sp?

0 Votes 0 ·

If you want to read easily, you can also add () before and after the statement. But it is also possible if we omit it:

 create procedure a_stu
 as
 (SELECT *
 INTO database.schema.table
 FROM database.schema.table)
                   
 execute a_stu 

Echo

0 Votes 0 ·

are we not using braces around to start and end the sp?

You can have BEGIN END, but it is not compulsory. Braces as in C/C#/C++ are not used in T-SQL.

However, you need to be careful with the use of go and this is missing from Echo's examples.




0 Votes 0 ·