CREATE TABLE/VIEW from stored procedure or SELECT...

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

Look at SELECT ... INTO command.|||

use northwind

select * into #tablex from employees

select * from #tablex

|||

Sorry joeydj but your example create a copy of another table! I need to create a table that contains only a few columns from another table, so that why I need to use a SELECT or a stored procedure that build and execute a SELECT.

Can I do that?

Thanks!

|||Sorry gavrilenko_s but I miss your post! You are right! That is the solution! Thanks!|||

hi,

first you have to create a table that has a similar

structure with the Sp

and then you can use

insert into temp

exec sp1

here's a sample snippet

USE NORTHWIND
select 'my name.........................12345' as productname, 10000.00000
as unitprice, 10000.0000 as quantiTY,
10000.0000 as discount, 10000.0000 as extendedprice
into tempx

truncate table tempx

insert into tempx
exec dbo.CustOrdersDetail '10248'

select * from tempx
drop table tempx

also suggest you make use of UDFs

cheers :)

Create table, table name as procedure parameter ?

Hi,

Is it possible to create a table in a stored procedure, where the table name

comes as a string procedure parameter?

Sorry, I am a newbie, maybe it is not possible this way,

but then what is the suggested way?

this results error in SQL Management Studio, if I press Parse.

>Incorrect syntax near '@.tableName'.

the "CREATE TABLE MyFixNameTable" line works, but it fixes the table name.

Code Snippet

CREATE PROCEDURE CreateMyTable

-- Add the parameters for the stored procedure here

@.tableName nvarchar(MAX) = ''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- CREATE TABLE MyFixNameTable

CREATE TABLE @.tableName

(

"^First Name" varchar(25) NOT NULL,

"^Last Name" varchar(25) NOT NULL

)

END

You can't supply an object name as a variable/parameter to a SQL statement.

However, you could create the entire SQL statement as a string, and then use sp_executesql to execute that string.

You may find this article useful:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Like Arnie saie, you cannot create a table like this. Generally speaking, it is rarely a good thing to be programatically creating permanent tables to start with. You can do this with dynamic sql, but why? If you are going to load the data with the results of a query, it is likely best for you to do something like:

select firstName, lastName
into yourTableName
from ...

It is usually faster and avoids some logging overhead. The best way to do this is usually to have a permanent table that includes some other column to denote when you searched for data, etc, some discriminator. Then you can work with the data in the same tables every time you do this, and you code is simplified, and the data is available more readily for reporting what is being done.

Create Table within an IF statement causes error

This doesn't make any sense to me. I am trying to create a stored procedure that creates a temp table using T-SQL. The table will be created differently depending on the arguments passed. Here is an example of what I am trying to do:

DECLARE @.Switch bit

SET @.Switch = 0

IF @.Switch = 0

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (Zero int)

END

ELSE

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (One int)

END

SELECT * FROM #DontWork

If you run this as is, it fails stating that "There is already an object named '#DontWork' in the database." However if you comment out one of the CREATE TABLE statements (either one of them), it works fine. The PRINT @.Switch line will prove that the IF ELSE statement is evaluating properly if you change the value of @.Switch. My guess is that the parsing engine is throwing the error before it even tries to run the code. Is there any way to make this work the way it should? Or do I have to resort to creating 2 different tables and modifying the rest of my code to compensate for the change?

This is usually caused because you have ran a CREATE TABLE statement in a previous development iteration. Try appending this to the end of your code:

Code Snippet

go

drop table #DontWork

The temp table stays in scope after you run through one time so the next time through you get the error. Try hiliting the code I've given you and execute just the DROP TABLE. Then un-hilite the code and rerun query. It should run correctly once you have dropped the table.

OK, I'm all wet... Hang on.

You can alter it:

Code Snippet

create table #what (one int)

alter table #what
add two int

alter table #what
drop column one

select * from #what

go

drop table #what

/*
two
--
*/

|||

The code doesn't execute.

The parsing engine is attempting to resolve the objects, and (incorrectly, in my opinion) assumes that the second instance of the create table is attempting to make a second object with the same name. The parsing engine is resolving objects, not checking logic and code flow.

To test, comment out EITHER CREATE statement and the code executes.

Your options include creating the #Temp table before the IF statement, or using a different #Table name in the second instance.

Or you could have both switched locations call out to another procedure that creates the #Temp table.

|||My code already involves altering the table. I was just trying to use an IF statement because one scenario creates a predictable table structure, and the other side requires that the field names be calculated at run time. I was trying to save myself some effort by simply having that CREATE TABLE command in there twice, but it seems that because of what Arnie said about the parsing engine resolving objects, not checking logic and code flow, I'm going to have to do things the complicated way. I wish there was a way to communicate things like this to the powers that be at Microsoft. Any idea how to do that, if at all possible?|||

They do pay attention to the suggestions.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||MS SQL has always had a problem with this construct. The solution, as mentioned, is to create the table once, and then use alter table to change the table to what you want. Or just create 2 tables of different names.

|||

SQL Server compiles the entire batch (SP, trigger, function or ad-hoc) and compilation doesn't take into account run-time information (variable values, control of flow etc). This gets tricky for temporary tables because of the way they are scoped. For best performance and manageability, you should put the creation logic for the different conditions in their own SPs and the execution logic too. This provides better reusability. You could use the ALTER TABLE approach but that will give bad performance in SQL Server 2005 since it negates the caching that we do automatically on temporary tables (metadata & 1 page of allocation which can get reused). Of course, if you can remove the temporary tables altogether.

Btw, your code will work if you were creating a permanent table conditionally.

Create Table with variable name

This should be simple, but...

I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....

CREATE PROCEDURE foo

-- Add the parameters for the stored procedure here

@.TableName char = null

AS

BEGIN

SET NOCOUNT ON;

CREATE TABLE @.TableName (

[HRMONTH] [int] NULL,

[HRYEAR] [int] NULL

) ON [PRIMARY]

But no combination of names '@.'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...

Thanks,

Tom

DECLARE @.ExecSQL NVARCHAR(300
SET @.ExecSQL = "CREATE TABLE @.TableName ..."
EXECUTE @.ExecSQL @.TableName

Remember that all variables have to be NVARCHAR and not VARCHAR. Also the exact syntax might be a bit off. In hat case use this as a reference. Hope this helps.|||

>>Remember that all variables have to be NVARCHAR and not VARCHAR

This is only true for sp_executesql, exec dynamic sql works with varchar also take a look at this example

declare @.table varchar(49),@.sql varchar(500)

select @.table ='Orders2006'
select @.sql = 'create table ' + @.table + '(id int)'
exec (@.sql)

exec('insert ' + @.table + ' values(1)')

exec('select * from ' + @.table)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Ahh. One thing to note about the above method is that I believe it may allows for more potentials for sql injections - may not be an issue with this but with queries and etc I believe it shoudl be avoided as opposed to the other method due to these security restrictions related to sql injection/execution.|||

There is always this

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

It deals with the whole thing, injections, permissions etc etc

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the responses. This worked well, until I read the article in the previous post. So, maybe this wasn't such a hot idea...

Thanks again,

Tom

|||

One way is to take below approach which doesn't require dynamic SQL:

create table _tmp (

...

)

exec sp_rename _tmp, @.name_passed_to_proc

Create table Stored Procedure

Hi,
Im tring to create a stored procedure with objective as
1) add new project details
2) create table with name as <projectcode>_MONTHSETTINGS
pls correct my below code
thanks in advance
-DNK
---
CREATE PROCEDURE [dbo].[AddNewProject]
(
@.pcod varchar(50) ,
@.pnam varchar(255),
@.keyl varchar(100),
@.cname varchar(255),
@.status varchar(10)
)
as
BEGIN
insert into ORS_PROJECTS
(CODE,PROJECTNAME,KEYLOCATION,CUSTOMERNA
ME,STATUS)
values (@.pcod,@.pnam,@.keyl,@.cname,@.status)
declare @.tabname varchar(255)
@.tabname = @.pcod + "_MONTHSETTINGS"
create table @.tabname (
monthname varchar(50),
targetamount float,
unitcost float )
return @.@.error
END
GODoesnt work for DDL you have tot put it in dynamicSQL:
DECLARE @.SQLSTRING VARCHAR(4000)
SET @.SQLSTRING = 'CREATE TABLE ' + @.tabname '( monthname VARCHAR(50),
targetamount float, unitcost float )'
EXEC(@.SQLSTRING)
http://www.sommarskog.se/dynamic_sql.html
HTH; Jens Suessmeyer.|||you should use dynamic sql to create the table.
"DNKMCA" <dnk@.msn.com> wrote in message
news:OMKqw380FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Im tring to create a stored procedure with objective as
> 1) add new project details
> 2) create table with name as <projectcode>_MONTHSETTINGS
> pls correct my below code
> thanks in advance
> -DNK
> ---
> CREATE PROCEDURE [dbo].[AddNewProject]
> (
> @.pcod varchar(50) ,
> @.pnam varchar(255),
> @.keyl varchar(100),
> @.cname varchar(255),
> @.status varchar(10)
> )
> as
> BEGIN
> insert into ORS_PROJECTS
> (CODE,PROJECTNAME,KEYLOCATION,CUSTOMERNA
ME,STATUS)
> values (@.pcod,@.pnam,@.keyl,@.cname,@.status)
> declare @.tabname varchar(255)
> @.tabname = @.pcod + "_MONTHSETTINGS"
> create table @.tabname (
> monthname varchar(50),
> targetamount float,
> unitcost float )
> return @.@.error
> END
> GO
>|||Why would you create a new table for each project? The obvious solution
would be to have one table for all projects with a project_code column.
David Portas
SQL Server MVP
--|||Most sensible reason is so you can apply different security permissions on
each table.
That way you can restrict project information to the people who are working
on it.
But it doesn't seam to be the case in this instance.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129631841.002751.28740@.g43g2000cwa.googlegroups.com...
> Why would you create a new table for each project? The obvious solution
> would be to have one table for all projects with a project_code column.
> --
> David Portas
> SQL Server MVP
> --
>|||"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d054$0$137$7b0f0fd3@.mistral.news.newnet.co.uk...
> Most sensible reason is so you can apply different security permissions on
> each table.
> That way you can restrict project information to the people who are
working
> on it.
>
Wouldn't one table with multiple views be the preferred way to handle access
to data?|||Yes. Alternatively, if the requirement is to support a partitioned view
then the project code is almost certainly a bad choice for a
partitioning column. It's unwise to choose a partition that forces
table creation under user control rather than by the administrator.
David Portas
SQL Server MVP
--

create table => system table

Hallo everybody,
when I create a table or a stored procedure it always becomes a system
object instead of a user object, who can I avoid this? I just want to create
user objects.
I'm using SQL Server 2000 and I have all the service packs installed, the
user I'm using to create the table is DBOwner of the database and is part of
the role "system administrators"
thanks for the help
CristianSomebody has been playing with the sp_MS_upd_sysobj_category procedure. Exec
ute it with the value 2
as parameter and verify that objects create from thereon will not be system
objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cristian" <cristiansuazo@.hotmail.com> wrote in message news:O0iCVmKWGHA.752@.TK2MSFTNGP02.p
hx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>|||what do you mean by system object. How did you find that it was a system
object?
"Cristian" wrote:

> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>
>|||What makes you think they're system objects?
*mike hodgson*
http://sqlnerd.blogspot.com
Cristian wrote:

>Hallo everybody,
>when I create a table or a stored procedure it always becomes a system
>object instead of a user object, who can I avoid this? I just want to creat
e
>user objects.
>I'm using SQL Server 2000 and I have all the service packs installed, the
>user I'm using to create the table is DBOwner of the database and is part o
f
>the role "system administrators"
>thanks for the help
>Cristian
>
>|||thanks man! that resolved everything... strange stored procedure, an
undocumented one, anyway thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8cZjzKWGHA.3972@.TK2MSFTNGP02.phx.gbl...
> Somebody has been playing with the sp_MS_upd_sysobj_category procedure.
Execute it with the value 2
> as parameter and verify that objects create from thereon will not be
system objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
create
the
part of
>|||Perhaps trace flag 1717 is on. You can interrogate with DBCC TRACESTATUS
(1717) and turn off with DBCC TRACEOFF (1717, -1) . Remove it from startup
parameters, if present.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to
> create
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
> of
> the role "system administrators"
> thanks for the help
> Cristian
>

Create stored procs in new DB from within a stored proc?

In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:

> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:

> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.
On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:

>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:
>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create stored procedure wizard in 2005?

Where is the create stored procedure wizard in SMS 2005 that we had in 2000?!

The templates are nice, but they show syntax only. The 2000 wizard created insert update and delete stored procedures based on the table structure.

Regards Richard

Looks like it has been removed, which is a real shame. The templates aren't much use to me - I know how to write SQL, but they are tedious when they are simple insert, update and delete statements. Looks like I'll have to write my own little app to do them for me :-(

Pete

|||I'm now using CodeSmith...|||

Inside SQL 2005 Management Studio, expand the database (where you want to create your stored procedure) then expand programability, then on stored procedures right click and choose new stored procedure ( the long way.) You should also check out the tempate explorer in SQL Management Studio. Press Ctl-Alt-T and you will see the template explorer come up on the right. You will see that there are a lot more templates than in SQL 2000

quoted from "http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=181070&SiteID=1"

Create stored procedure wizard in 2005?

Where is the create stored procedure wizard in SMS 2005 that we had in 2000?!

The templates are nice, but they show syntax only. The 2000 wizard created insert update and delete stored procedures based on the table structure.

Regards Richard

Looks like it has been removed, which is a real shame. The templates aren't much use to me - I know how to write SQL, but they are tedious when they are simple insert, update and delete statements. Looks like I'll have to write my own little app to do them for me :-(

Pete

|||I'm now using CodeSmith...|||

Inside SQL 2005 Management Studio, expand the database (where you want to create your stored procedure) then expand programability, then on stored procedures right click and choose new stored procedure ( the long way.) You should also check out the tempate explorer in SQL Management Studio. Press Ctl-Alt-T and you will see the template explorer come up on the right. You will see that there are a lot more templates than in SQL 2000

quoted from "http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=181070&SiteID=1"

Create stored procedure wizard in 2005?

Where is the create stored procedure wizard in SMS 2005 that we had in 2000?!

The templates are nice, but they show syntax only. The 2000 wizard created insert update and delete stored procedures based on the table structure.

Regards Richard

Looks like it has been removed, which is a real shame. The templates aren't much use to me - I know how to write SQL, but they are tedious when they are simple insert, update and delete statements. Looks like I'll have to write my own little app to do them for me :-(

Pete

|||I'm now using CodeSmith...|||

Inside SQL 2005 Management Studio, expand the database (where you want to create your stored procedure) then expand programability, then on stored procedures right click and choose new stored procedure ( the long way.) You should also check out the tempate explorer in SQL Management Studio. Press Ctl-Alt-T and you will see the template explorer come up on the right. You will see that there are a lot more templates than in SQL 2000

quoted from "http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=181070&SiteID=1"

sql

create stored procedure in IF-structure

Hi everyone,

I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:

Code Snippet

USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'au_info2' AND type = 'P')
DROP PROCEDURE au_info2
GO
USE pubs
GO
CREATE PROCEDURE au_info2
@.lastname varchar(30) = 'D%',
@.firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN titleauthor ta
ON a.au_id = ta.au_id INNER JOIN titles t
ON t.title_id = ta.title_id INNER JOIN publishers p
ON t.pub_id = p.pub_id
WHERE au_fname LIKE @.firstname
AND au_lname LIKE @.lastname
GO

The thing is, I want to change and use it like this:

Code Snippet

USE pubsIF NOT EXISTS (SELECT name FROM sysobjects


CREATE PROCEDURE ...

USE pubs

GO

ALTER PROCEDURE au_info2 ...

But that does not seem to work.. I get the following error:

Code Snippet

Incorrect syntax near the keyword 'PROCEDURE'

Any idea's? Any help is appreciated!

Kind regards,

Frederik

The CREATE statement needs to be the first statement in the batch so you can't have it in after an IF clause.

I guess you could get round this by doing the following:

IF NOT EXISTS.....

EXEC('CREATE PROCEDURE au_info2 AS.....')

HTH!

|||Very dirty, but it works! I need it to avoid some errors when

replicating and such.. Thx!

Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
Thanks
I don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:

> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>
|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help
|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:

> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>

Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
ThanksI don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:
> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:
> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>

Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
ThanksI don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:

> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:

> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>

Create stored procedure

Hi!
I have a problem. I would like to create a stored procedure from a script file. I must use inparameters as well. I'm using ms Access 2000.
Please help me!
Mike.I'm not sure that I understand what you mean by inparameters, but if you just read the script file into a string variable, then execute that string variable as a command, then you should be "good to go".

-PatP|||Why would you want to do backend application development from access..

I would imagine it would be severe hoop jumping...

Get the sql server client side tools...

unless we're really talking about MSDE...|||Originally posted by Brett Kaiser
Why would you want to do backend application development from access.. Why do some folks like leather undies? There is no accounting for taste.

I'd suggest using OSQL or better yet Visual Studio, but that's just me!

-PatP|||Originally posted by Pat Phelan
Why do some folks like leather undies?

I have no response|||We are currently (trying) to create an application with Access forms and SQL server database with stored procedures. If you can get out of it, please do. Certainly the part with the stored procedures parameters is a hell. I would also suggest MSDE with osql.

But, probably, you can't drop the Access, so if you can supply some more info and i'll look into it.sql

Create Store Procedure Fails "Incorrect syntax near the keyword 'ON'."

Hi All!

I'm really new to SQL environment in general so, sorry if this is a stupid question.

I'm trying to create a Stored Procedure on my BD with SQL Server Management Studio Express.

I receive this error:
Msg 156, Level 15, State 1, Procedure sprocBlogEntrySelectListByCategory, Line 18
Incorrect syntax near the keyword 'ON'.

This is the sp:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories ON BlogPosts.bp_ID = PostInCategories.bp_ID INNER JOIN

Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

GO

I really don't understand this error and what does this means in my case.

Any suggestion is appreciated.

alan

It seems to me that the error is in your FROM clause. FROM must be followed with a table name, derived table, or view.

In your case, you have a FROM clause followed by a JOIN condition without the JOIN clause.

|||I am guessing you mean this:

CREATE PROCEDURE sprocBlogEntrySelectListByCategory

@.categoryId int

AS

BEGIN

SET NOCOUNT ON;

SELECT

BlogPosts.bp_ID,

BlogPosts.bp_Title,

BlogPosts.bp_Body,

BlogPosts.bp_DatePublished,

Categories.cat_Name

FROM

PostInCategories

INNER JOIN BlogPosts ON BlogPosts.bp_ID = PostInCategories.bp_ID

INNER JOIN Categories ON PostInCategories.cat_ID = Categories.cat_ID

WHERE

(PostInCategories.cat_ID = @.categoryId)

ORDER BY

BlogPosts.bp_DatePublished DESC

END

hth.

http://www.elsasoft.org|||

I tryed to re-write the sp in SQL Mgm Studio from scratch.
Identical to that one I posted earlier in my opening, and it was accepted without problem.

I think there was same TAB, SPACE or Comma character wrong.

I think my "issue" is resoved.

Thanks for yuor help anyway

Alan.

Create SQL Server Objects from Command Prompts

Hi

Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??

If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts

How Can I do that .. ??

And thanks with my regarding

FraasHave a look at OSQL in SQL Server BOL

Create SP denied ?

We added a user to a database with db_datareader, db_datawriter and Create Procedure Privilages .. while creating stored procedures using dbo. prefix he gets an error

Server: Msg 2760, Level 16, State 1, Procedure test4, Line 3
Specified owner name 'dbo' either does not exist or you do not have permission to use it.

What is the problem. We don't want him to create procedures with his uid as prefix .. Please helpYou need to put him/her in the dbo_owner role...|||Is it necessary ? If he is dbo he doesn't need to specify dbo. while creating objects . I don't want him to be a dbo|||If yo uwant him to vcreate objetcs as dbo (the owner) he has to have the rights to be in the group...

Or...

He can develop in his own db you set up for him/her...make the dbo in that group (or not) and let them create objects without hard coding the owner...

Then have him give you the scripts and you compile them.

What'dya think?sql