New Table 1

[codrutza]

Hi. I'm trying to build a table for a report.
USE [Data003]
GO
/****** Object: StoredProcedure [dbo].[Z_CodrCarriers] Script Date: 10/30/2012 12:23:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;

SELECT Suppliers.Code as SupCode
INTO dbo.CodrCarriers_T
FROM Suppliers
WHERE Suppliers.Code LIKE 'S%'

I don't know if I wrote right, but the table CodrCarriers_T
doesn't appear among the other tables created by programmers.
What do I do wrong, please?

 

[djj55]

But then you cannot create the table each time.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[flapeyre]

True, but he should consider the overhead of dropping and recreating a table each time.

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[SQLBill]

flapeyre,

The GO should have 'closed' the stored procedure and the EXEC statement wouldn't be part of it. It would run after the stored procedure was created. At least that's how it works with every stored procedure I've created so far.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[flapeyre]

The GO should have 'closed' the stored procedure and the EXEC statement wouldn't be part of it.

True. However, the way it was written (with the DROP PROCEDURE commented out), the SP would be created only the first time. After that, since it already exists, you'd get an error.

He was getting these errors:

Msg 4104, Level 16, State 1, Procedure Z_CodrCarriers, Line 10
The multi-part identifier "dbo.CodrCarriers_T" could not be bound

Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'dbo.Z_CodrCarriers'.

Msg 208, Level 16, State 1, Line 5
Invalid object name 'CodrCarriers_T'.

Not sure if [tt]IF OBJECT_ID([dbo].[CodrCarriers_T]) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T][/tt]
is the correct syntax or not. I'm waiting for SSMS to be installed on here, so I can't play with it right now, but if I remember correctly, you can:

[ol 1]
[li]Create the table using the designer (or whatever)[/li]
[li]Refresh the Tables in SSMS for that database[/li]
[li]Right-click on the table and select DROP and CREATE to... Clipboard[/li]
[li]Paste that into the script.[/li]
[/ol]

-- Francis
There are laws to protect the freedom of the press's speech, but none that are worth anything to protect the people from the press.
--Mark Twain

 

[SQLBill]

Right....we commented out the DROP PROCEDURE since it didn't exist yet. For some reason the stored procedure isn't being created and we've been working on determining why not.

codrutza,

try this change, I removed the dbo and the square brackets.

IF OBJECT_ID(CodrCarriers_T) IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[codrutza]

wrote

USE [Data003]
GO
/****** Object: StoredProcedure [dbo].[Z_CodrCarriers] Script Date: 12/04/2012 12:45:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--now we will create the stored procedure
create PROCEDURE [dbo].[Z_CodrCarriers]
AS

SET NOCOUNT ON;


--drop table [dbo].[CodrCarriers_T]
--Check if table exists, if so, drop it
IF OBJECT_ID('dbo.CodrCarriers_T') IS NOT NULL
DROP TABLE [dbo].[CodrCarriers_T]

CREATE TABLE [dbo].[CodrCarriers_T] ([SupAcc] CHAR(9), [SupCode] CHAR(15))

INSERT INTO [dbo].[CodrCarriers_T] ([SupAcc], [SupCode] )
(SELECT Suppliers.AccountNumber, Suppliers.

FROM [dbo].[Suppliers]
WHERE Suppliers.Code LIKE 'SL%')


--we are done with the stored procedure and need to 'close' it
GO
--now we execute (run) the stored procedure
EXEC [dbo].[Z_CodrCarriers] 

--now we gather the data from the stored procedure.
SELECT SupAcc, SupCode
FROM CodrCarriers_T

and doesn't give me errors and creates the table


Thank you very much, SQLBill, and thank you all for your replies