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 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 In Store Procedure and inserting data with the Kalen user.

Hello.
First: Sorry for my inglish.
I'need create this "simple" SP
I Work with (SQL 2000 + SP4)
Use TempDB
GO
Create procedure DBO.ProcTest
as
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to public
When I execute this SP whit the user Kalen (She have "Create table"
permission on TempDB) I receive this message
--Invalid object name 'Tempdb..TabTest'
This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
code in the Query analyzer, not have this problem.
The question is: Exist a solution without SQL Dynamic, with persistent table
and creating only one Procedure (not a procedure for each user)?
Thank You
Fernando Flamenco
Ing Sistemas de Informacin
Buenos Aires - Argentina.First of all don't create tables in TempDB. TempDB is a system database.
"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:uD8UmEFgGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello.
> First: Sorry for my inglish.
> I'need create this "simple" SP
> I Work with (SQL 2000 + SP4)
> Use TempDB
> GO
> Create procedure DBO.ProcTest
> as
> drop table Tempdb..TabTest
> create table Tempdb..TabTest (a int)
> insert into Tempdb..TabTest values (1000)
> GO
> grant exec on DBO.ProcTest to public
> When I execute this SP whit the user Kalen (She have "Create table"
> permission on TempDB) I receive this message
> --Invalid object name 'Tempdb..TabTest'
> This is beacuse SQL find a Temp.DBO.TabTest or not?. but if execute the
> code in the Query analyzer, not have this problem.
> The question is: Exist a solution without SQL Dynamic, with persistent
> table and creating only one Procedure (not a procedure for each user)?
> Thank You
> Fernando Flamenco
> Ing Sistemas de Informacin
> Buenos Aires - Argentina.
>

Create Table In Store Procedure and inserting data with the Kalen user.

As Mike said, don't create objects in tempdb. However, your stored
procedure is failing because the first thing you are doing is dropping
a table that doesn't exist.
What are you trying to accomplish?
StuThank stu and mike,
I' know: the tempdb is a system database but i use this database like
workspace.
I explain this again, for this
1) Create a user Kalen with the create table permission on tempdb database
2) Connect with this user and execute
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TabTest]
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
This code is simple and the more important work
3) Connect with a user sa and create this SP
Create procedure DBO.ProcTest
as
if exists (select * from dbo.sysobjects where id =
object_id(N'[TabTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Tempdb..TabTest
create table Tempdb..TabTest (a int)
insert into Tempdb..TabTest values (1000)
GO
grant exec on DBO.ProcTest to Kalen
4) Connect with the Kalen user this not work
I receive this message
--Invalid object name 'Tempdb..TabTest'
The question is:
I need create this one SP and not use SQL Dynamic.
Easy'
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1148603889.848594.234520@.j73g2000cwa.googlegroups.com...
> As Mike said, don't create objects in tempdb. However, your stored
> procedure is failing because the first thing you are doing is dropping
> a table that doesn't exist.
> What are you trying to accomplish?
> Stu
>|||"Fernando Flamenco" <flamencof@.yahoo.com> wrote in message
news:%23s7zNOMgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Thank stu and mike,
> I' know: the tempdb is a system database but i use this database like
> workspace.
<snip> SQL Server uses it as a workspace as well. You're asking for trouble
here. Have fun.</snip>

> I need create this one SP and not use SQL Dynamic.
1) I HIGHLY recommend against forcing your own DDL inside TempDB. At best
you're creating contention within SQL Server for TempDB resources. It's a
System Database for a reason.
2) I don't see the point of what you're doing, but if you're building the
SP in a separate Database from the one you're executing it in (assuming
that's the reason you feel the need to prefix the table with Tempdb..
everywhere), then you need to look at the effect of not using that prefix in
your IF EXISTS statement. I added the database name prefix to your select *
from dbo.sysobjects and object_id statements and dropped the OBJECTPROPERTY
function in favor of checking the XTYPE column. Works great.

> Easy'
Too Easy.sql

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 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 to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION

create store procedure to incremental populate full text index tab

I need to create a store procedure to perform incremental population of full
text index table. The store procedure will be call from my program.
Any ideas ?
xxx
This is probably what you want.
exec sp_fulltext_table N'[dbo].[authors]', N'start_incremental'
I suggest you have a look at change tracking and schedule the index updates.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:AEAD8DB8-91F6-484A-AD2C-C6AB06E5B38B@.microsoft.com...
>I need to create a store procedure to perform incremental population of
>full
> text index table. The store procedure will be call from my program.
> Any ideas ?
> --
> xxx

create store procedure that take data from 2 database

hello all..,

i want to make procedure can decreasetotalcost from order table(database:games.dbo) withbalance in bill table(database:bank.dbo). my 2 database in same server is name "boy"

i have 2 database like: bank.dbo and games.dbo

in games.dbo, have a table name is order(user_id,no_order,date,totalcost)

in bank.dbo, have a table name like is bill(no_bill,balance)

this is a list of bill table

no_bill balance

111222 200$

222444 10$

this is a list of order table

user_id no_order date totalcost

a 1 1/1/07 50$

when customer insert no_bill(111222) in page and click a button, then bill table became

no_bill balance

111222 150$

222444 10$

when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"

is procedure can take data from 2 database?

mystore procedure like:

ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.balanc AS money

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill

COMMIT TRANSACTION
END

it's output message "Invalid object name '<boy\sqlexpress>.Bank.dbo.bill'.
Transactioncount after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTIONstatement is missing. Previous count = 0, current count = 1.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[pay]."

plss.. help...

Hi,

Just remove <boy\sqlexpress>.section, it would run. If two databases are on same server, they don't need to reference instance name with them.

|||

thx for information...

i have remove it, but it can not work too...

SET @.balanc= (SELECT [balance] FROM [Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

the error is same... another ways?

plzz...,help..

|||

What database is the pay stored procedure in?

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

When setting the balanc variable you reference the table with [boy\sqlexpress.Bank.dbo.bill], but in the update statement you use UPDATE [bill]

|||

did you try [Bank].[dbo].[bill] instead of [Bank.dbo.bill] ?

|||

thx for dleonard and david information...

i have try it all... but cannot too...

[boy\sqlexpress.bank.dbo.bill]

[boy\sqlexpress].[bank].[dbo].[bill]

[bank.dbo.bill]

[bank].[dbo].[bill]

[bank.bill]

[bank].[bill]

[bank.dbo].[bill]

[boy\sqlexpress.bank.dbo].[bill]

[bill.bank.dbo]

i am create store procedure inboy\sqlexpress.games.dbo.

i haveboy\sqlexpress.bank.dbo andboy\sqlexpress.games.dbo

i have post this in this forums 3 thread, but it's cannot resolved...

pls... another idea?

or maybe store procedure can not get data from 2 database?

|||

hardy:

or maybe store procedure can not get data from 2 database?

I know it can be done with SqlServer. Don't use SqlExpress...

|||

Oh! I just re-read your example and noticed the BEGIN TRANSACTION / COMMIT TRANSACTION commands in it.

That's probably why it's barfing.

I would be surprised if SqlExpress supported transactions across databases. If I'm right, removing those two commands would enable you to proceed.

(Of course, it would also open you up to the dangers of the transaction failing half-way thru and leaving the first half committed in the other database.)

|||

|||

it can not work too...

the errors is focus that bill table can not be found...

the other command is already right... now big problem is [bank.dbo.bill].

maybe connection string?

|||

Let say you have two tables (Table1 in Database1 owned by User1) and (Table2 in Database2 owned by User2)

Make sure that each time you call any object .. call it with its full name: [ServerName].[DatabaseName].[OwnerName].[ObjectName]

Example:

SELECT *FROM Server1.Database1.User1.Table1 aINNERJOIN Server2.Datbase2.User2.Table2 bON a.col1 = b.col1

Good luck.

|||

thx for ur code...

can u make it to my code?

my code in procedure:

ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.balanc AS money

SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)

UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill

COMMIT TRANSACTION
END

i only want to call bank.dbo. because i create a store procedure in games.dbo, so i dont need call a games.dbo..

now, the problem is bank.dbo can not be detected... any ideas?

|||

Note: I assumed thatpay stored procedure exists in the Bank database, try this:

ALTER PROCEDURE [bank].[dbo].[pay] @.no_billINT, @.no_orderint, @.totalcostmoneyASBEGIN BEGIN TRANSACTION DECLARE @.balancmoney SELECT@.balanc= [balance]FROM [bank].[dbo].[bill]WHERE [no_bill] = @.no_billUPDATE [bank].[dbo].[bill]SET [balance] = (@.balanc - @.totalcost)WHERE [no_bill] = @.no_billCOMMIT TRANSACTIONEND

Good luck.

|||

yess, many thx cs4ever. u are great...

my wrong yesterday is i make in select[bank].[dbo].[bill], i make in update

[bank.dbo.bill]. it cannot be work. i think [bank.dbo.bill] is similar with[bank].[dbo].[bill].i just know that it no similar...

so it must [bank].[dbo].[bill], can work...

and thx to david too, u ways is true too.but u not give example..., so i try ur ways in select not in update...

ok, thx to all.. this day is so great.....