CODEWITHSUNDEEP
sql-server-2008stored-procedures
IrfanRaza
IrfanRaza

Reputation: 3058

What's wrong with the stored procedure

I have a table called Std_Components which acts like an index for list of components with associated tables. The column AssociatedTable holds the name of table that actually contains the component data.

Please check images below -

enter image description here

Here is table data for Std_SteeringPumps

enter image description here

I am trying to create a stored procedure that will copy Std_Components table as well as all associated tables with new name. For ex. Lets say if i provided 001 as a parameter to this stored procedure i should be able create new tables like C001_Components, C001_SteeringPumps and so on.

This is what I have done so far:

ALTER PROCEDURE [dbo].[sgi_sp_CreateTablesForNewCompany]
    -- Add the parameters for the stored procedure here
    @CompanyId varchar(5)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   -- declare variables
    declare @qry as varchar(2000)
    declare @compTblName as varchar(100)
    set @compTblName = 'C'+@companyId +'_Components'

    -- Check if table already exists
    IF object_id(@compTblName) is not null
        return

     -- Create main component index table by copying standard component table --
    set @qry = 'Select * into '+@compTblName+' From Std_Components;';
    --print @qry
    --execute (@qry)

    set @qry =@qry + 'Update C'+@companyId +'_Components Set AssociatedTable=''C'+@companyId +'''+substring(AssociatedTable,4,200);';
    --print @qry
    --exec @qry

    -- Create all child tables --
    Select * Into #TempTbl From dbo.Std_Components

    Declare @Id int

    While (Select Count(*) From #TempTbl) > 0
    Begin

        declare @rowTableName as varchar(50)
        declare @compNewTbl as varchar(50)

        Select Top 1 @rowTableName=AssociatedTable, @Id = Id From #TempTbl
        set @compNewTbl = 'C'+@companyId + substring(@rowTableName,4,200);

        set @qry = @qry + 'Select * into '+@compNewTbl+' From ' + @rowTableName + ';'
        --print @qry
        --exec @qry

        Delete #TempTbl Where Id = @Id

    End

    print @qry
    exec @qry
END

Here is the output of the print statement for the query it generates -

Select * into C001_Components From Std_Components;
Update C001_Components Set AssociatedTable='C001'+substring(AssociatedTable,4,200);
Select * into C001_SteeringPumps From Std_SteeringPumps;

But when the stored procedure is executed, I get the following error -

Msg 203, Level 16, State 2, Procedure sgi_sp_CreateTablesForNewCompany, Line 56
The name 'Select * into C001_Components From Std_Components;Update C001_Components Set AssociatedTable='C001'+substring(AssociatedTable,4,200);Select * into C001_SteeringPumps From Std_SteeringPumps;' is not a valid identifier.

Can anybody help me out resolve this issue.

Thanks for sharing your time and wisdom.

Upvotes: 1

Views: 78

Answers (3)

StuartLC
StuartLC

Reputation: 107247

Instead of 

exec @qry;

You need

exec sp_executesql @qry;

You'll also need to change the type of @qry to NVARCHAR. Note that because of the dynamic sql, the proc is prone to SQL Injection and other escaping issues (i.e. ensure that @CompanyId is validated)

Upvotes: 1

Paul Hunt
Paul Hunt

Reputation: 3555

The error you're getting is because the EXEC statement (the last line of the stored procedure) needs to have brackets around the @qry variable so that it becomes

exec(@qry)

Without the brackets it's treating the entire SQL string as stored procedure name.

Upvotes: 3

Mike
Mike

Reputation: 2559

The non valid indentifier is around the AssociatedTable part

Set AssociatedTable='C001'+substring(AssociatedTable,4,200); will not run as there is no scope for AssociatedTable to substring - the string needs to contain the name of the table completely to be able to be executed

Upvotes: 1

Related Questions