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
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
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.
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.)
Thanks,
i am looking for SP without parameter,
if you could please advise.
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.
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.
Thanks for the reply,
are we not using braces around to start and end the sp?
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
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.
13 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index