CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
octobot
octobot

Reputation: 89

Error When Calling Procedure In SQL Server

I am trying to create a procedure in SQL Server. Here's my attempt so far:

create proc sp_cr_tbl(@table_name text)
--returns integer
as
begin
declare @sql nvarchar(4000)     
set @sql = 'create table @table_name(id integer)'
exec sp_executesql @sql
end

it created successfully but when I call 

EXEC sp_cr_tbl 'tbl_9'

I am getting following error:

ErrorMessage: Incorrect syntax near '@table_name'.

I am new to SQL Server, I was developing the same thing in PostgreSQL

Upvotes: 0

Views: 90

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

Change the code of your procedure to:

CREATE PROCEDURE cr_tbl (@table_name NVARCHAR(30))
AS
BEGIN
    DECLARE @sql NVARCHAR(4000)

    SET @sql = 'create table ' + @table_name + ' (id integer)'

    EXEC sp_executesql @sql
END

This is dynamic SQL and you need to append the name of the table you want to create to the command query (@sql) and then execute this.

You can add a PRINT @sql statement before EXEC to see exactly how the query will look like before it executes.

Also, I recommend that you do not use TEXT or NTEXT datatypes and pass the variable as NVARCHAR, because you've also set the datatype for @sql variable as NVARCHAR, and also give the parameter a size in the declaration of the procedure.

Also, take note of what @marc_s said, do not use sp as a prefix for your stored procedures as it has been reserverd by Microsoft.

Thus, in order to call the above stored procedure I recommend you use:

EXEC cr_tbl @table_name='tbl_9'

Advice:

It's not mandatory to specify the name of the parameter to which you are passing the variable, but I think it would come in handy in the future if you create stored procedures with multiple parameters and you want to pass multiple variables.

It will help to avoid errors if you explicitly specify what variables you pass to what parameters (otherwise, if you don't then the order of the parameters you pass will matter!).

Upvotes: 2

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Below Query will help you

create proc sp_cr_tbl(@table_name text)
 --returns integer
as
begin
 declare @sql nvarchar(4000)     
  set @sql = 'create table ' + convert(nvarchar(4000), @table_name) + ' (id integer)'
  exec sp_executesql @sql
end

Upvotes: 0

Related Questions