October 8, 2002 at 10:09 am
Someone please help...
I am trying to create a stored procedure to create a table (not temporary), and I'm not having any luck.
I am calling a universal stored procedure which I pass: a select query, a table name and the number of rows. This stored procedure creates a table with the passed table name containing n rows (which is also passed), then calls a remote stored procedure
passing the query which was passed to it. Upon receiving the results from the RPC, I am trying to insert into the newly created table the results I have received. This is where the problem is. I am receiving errors when trying to insert the results.
I cannot use temporary tables because the tables hold report info for users on a central database and they need to be present if the user opens another report and returns to this one. I have a routine which drops these table when the user exits the system. Any help would be greatly appreciated as I am fairly new to SQL. Thanks in advance.
October 8, 2002 at 10:32 am
Can you post the errors and/or the stored procedures being called? Do you know if the problem is with the first stored procedure or the second? If you know which one then you will only need to post that one.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 11:00 am
Here is the stored procedure giving me the problem. Check out the condition where @numcols = 9. I am executing a RPC that returns 9 fields, it creates the table properly, but I can't get it to insert the results. If you have suggestions to make this more efficient and to get it to work I would be ecstatic. Thanks for your prompt reply.
-- Trying to create a universal stored proc that executes a sql statement and builds a table holding the output results
CREATE PROCEDURE spUniversal @param1 varchar(2048), @tblname varchar(50), @numcols smallint
as
SET NOCOUNT ON
DECLARE @cmd VARCHAR(500), @sql varchar(80), @cmd2 varchar(80)
IF OBJECTPROPERTY(OBJECT_ID(@tblname), 'IsTable') IS NULL
BEGIN
GOTO build
END
ELSE
BEGIN
SET @cmd = 'DROP TABLE ' + @tblname
EXEC(@cmd)
GOTO build
END
build:
IF @numcols = 1
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, col1 varchar(100) NULL)'
END
IF @numcols = 2
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL)'
END
IF @numcols = 3
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL)'
END
IF @numcols = 4
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL)'
END
IF @numcols = 5
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL)'
END
IF @numcols = 6
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL, ' +
'col6 varchar(100) NULL)'
END
IF @numcols = 7
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL, ' +
'col6 varchar(100) NULL, col7 varchar(100) NULL)'
END
IF @numcols = 8
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL, ' +
'col6 varchar(100) NULL, col7 varchar(100) NULL, col8 varchar(100) NULL)'
END
IF @numcols = 9
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL, ' +
'col6 varchar(100) NULL, col7 varchar(100) NULL, col8 varchar(100) NULL, col9 varchar(100) NULL)'
EXEC(@cmd)
--INSERT @tblname
--EXEC GREP05.icecap.dbo.spExecuteSql @param1
SET @sql =
'INSERT ' + @tblname
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1
EXEC(@sql)
END
IF @numcols = 10
BEGIN
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED, ' +
'col1 varchar(100) NULL, col2 varchar(100) NULL, col3 varchar(100) NULL, col4 varchar(100) NULL, col5 varchar(100) NULL, ' +
'col6 varchar(100) NULL, col7 varchar(100) NULL, col8 varchar(100) NULL, col9 varchar(100) NULL, col10 varchar(100) NULL)'
END
GO
October 8, 2002 at 11:08 am
what error(s) do you get when executing this SP?
Robert W. Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 11:14 am
When I call this sp in SQL Query Analyzer I get the following:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'temp_2_99'.
temp_2_99 is the table name I supply as a parameter to this sp, but it does create it.
October 8, 2002 at 11:36 am
I have not set this up to run yet, but I have looked at the code. The following code toward the end stands out:
SET @sql =
'INSERT ' + @tblname
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1
EXEC(@sql)
Is this supposed to be your INSERT statement?
If so then I think you need to change it so that it looks like this:
SET @sql =
'INSERT INTO ' + @tblname
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1
EXEC(@sql)
All I changed was to add INTO after the word INSERT. If that doesn't help I can try and get this SP to run and see if I can identify the problem.
Also when I deal with dynamic SQL I find it useful to use the PRINT command to see what is being executed just prior to execution. So I would put PRINT @cmd just before each EXEC (@cmd) so that I can see what is about to be executed.
Also what is this SP call supposed to do: EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1?
It gets executed between creating the string @sql and executing @sql.
Robert W. Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 11:48 am
I added the INTO to the statement, but I get the exact same error. I only omitted it because I had seen some examples where it was not used.
I'll try putting the PRINT statements in to see what is happening (should be interesting).
The SP call you asked about is the RPC I use to fetch data from a remote SQL Server. I want to take these results and populate the created table with them so they are in this local database. This local database is where I want to accumulate data from several remote servers for our users so the execution time is minimized. It is actually pulling the data over, because I can see it in Query Analyzer, but then the SP chokes trying to put it in the table I have created.
October 8, 2002 at 11:56 am
If the intent of this section is to run something like this:
INSERT INTO tablename
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1
It is failing because it is actually executing this:
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1
INSERT INTO tablename
You would need to modify that section to look like this:
SET @sql =
'INSERT INTO ' + @tblname + '
EXEC SERVERNAME.DATABASENAME.dbo.spGetEvents @param1'
EXEC(@sql)
With putting in the PRINT commands you will be able to see this.
Robert W. Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 12:05 pm
It worked!
Thanks for all your help! I am going to start using PRINT more often for debugging my SPs. I have been trying to find code on the internet for a while now to show me how to do this to no avail.
Now, for one more question, if you don't mind. Is there a more efficient way of building this table instead of using so many IF statements? If you can help steer me in the right direction I would appreciate it.
Again, thank you.
October 8, 2002 at 12:12 pm
This might work for you after you modify it a bit more:
DECLARE @numcols tinyint,
@count tinyint,
@cmd varchar(200),
@tblname varchar(50)
SET @tblname = 'test'
SET @cmd = 'CREATE TABLE ' + @tblname + '(colid int IDENTITY(1,1) PRIMARY KEY CLUSTERED'
SET @count = 1
SET @numcols = 5
WHILE @count <= @numcols
BEGIN
SET @cmd = @cmd + ', col' + LTRIM(STR(@count)) + ' varchar(100) NULL'
SET @count = @count + 1
END
SET @cmd = @cmd + ')'
PRINT @cmd
Robert W. Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 8, 2002 at 12:21 pm
Thanks again.
That will make my SP code so much more compact and easier to manage.
You have been a tremendous help!
October 8, 2002 at 12:33 pm
Glad to have been of service.
Robert W. Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply