Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a variable to hold a table/view name

Hello all,
Is it possible to create a variable in a SQL script to hold a table/view
name?
Alot of my scripts are union queries where each part refers to the same
table/view, and sometimes those sources need to be changed. It would be
nice if I could change the value of the variable instead of doing a find &
replace to change the source's name.
Thanks for any help anyone can provide,
Conan Kelly"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:8LHDh.49165$5j1.25117@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is it possible to create a variable in a SQL script to hold a table/view
> name?
> Alot of my scripts are union queries where each part refers to the same
> table/view, and sometimes those sources need to be changed. It would be
> nice if I could change the value of the variable instead of doing a find &
> replace to change the source's name.
> Thanks for any help anyone can provide,
> Conan Kelly
>
http://www.sommarskog.se/dynamic_sql.html
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating a Stored Procedure from 3 queries

Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.StageNameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GOHi

A few points:

Check out http://www.aspfaq.com/show.asp?id=2319 for multiple record sets.

All three queries look like they should be combined into one, if not, you
will need to use a cursor.

Your second query is trying to set variables and also return a result set,
which is not allowed.

It is not good to use select * in production code.

I think you should also check for errors and add a SET NOCOUNT ON.

John

"Satvic" <fuck@.spammers.com> wrote in message
news:bl64ub$6de$1@.ins22.netins.net...
Hi all,

Sorry for HTML, there is a lot of code & comments

I tried to create a stored procedure from 3 queries .. to reduce # of times
DB gets access from 1 asp page. The result procedure only works 1/2 way
(does return the rest of the SELECT statement) :(

Please help me figure out what stops it mid way?

I need it to return all the results from the SELECT statements AND the
number of rows (ScriptsNo) from the count(*):
Here is my stored procedure:

CREATE PROCEDURE csp_AuthorAccountInfo
@.CandidateID int,

AS

DECLARE @.ScriptsNo int, @.ManuscriptID int

SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
CandidateID = @.CandidateID

/* this is where it stops all the time :(
Theoretically speaking, next SELECT will only return 1 row with Candidate's
info*/

SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State, cn.Country
FROM Candidates c INNER JOIN
Manuscripts m ON
c.CandidateID = m.CandidateID INNER JOIN
Locations l ON
c.LocationID = l.LocationID INNER JOIN
cn ON
l.CountryCode = cn.CountryCode
WHERE c.CandidateID = @.CandidateID

/* next SELECT should normally return manu rows with Candidate's submitted
manuscripts */

SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted,
ns.Comments
FROM Manuscripts m INNER JOIN
ManuscriptStages ms ON m.ManuscriptID =
ms.ManuscriptID INNER JOIN
ManuscriptStageNames msn ON ms.StageNameID = msn.Stage
NameID
WHERE m.ManuscriptID = @.ManuscriptID
ORDER BY ms.DatePosted DESC

GO|||Satvic (fuck@.spammers.com) writes:
> CREATE PROCEDURE csp_AuthorAccountInfo
> @.CandidateID int,
> AS
> DECLARE @.ScriptsNo int, @.ManuscriptID int
> SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE
> CandidateID = @.CandidateID

It would probably be better to return the count as an output parameter:

CREATE PROCEDURE csp_AuthorAccountInfo @.CandidateID int,
@.ScriptsNo int OUTPUT AS

DECLARE @.ManuscriptID int

SELECT @.ScriptsNo = count(*) as ScriptsNo
FROM Manuscripts WITH (NOLOCK)
WHERE CandidateID = @.CandidateID

And don't use NOLOCK unless you understand exactly what you are doing.

> /* this is where it stops all the time :(
> Theoretically speaking, next SELECT will only return 1 row with
> Candidate's info*/
> SELECT c.*, l.LocationID, @.ManuscriptID=m.ManuscriptID, l.State,
> cn.Country
> FROM Candidates c INNER JOIN
> Manuscripts m ON
> c.CandidateID = m.CandidateID INNER JOIN
> Locations l ON
> c.LocationID = l.LocationID INNER JOIN
> cn ON
> l.CountryCode = cn.CountryCode
> WHERE c.CandidateID = @.CandidateID

As John Bell pointed out, you cannot assigned variables and return
result sets in the same query.

And your comment that the SELECT would only return one seems a bit funny.
I don't know your tables, but if this row returns 1 row, then the
SELECT COUNT(*) always returns 1.

I don't know about your tables, but I would guess that a Candidate can
zero or more Manuscripts, in which case the above could return 0 or
more rows.

Since I don't know the data you are accessing, it is difficult to give
precise advice, but you should probably investigate the use of temp
tables and table variables.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create View with 2 Select Queries

Hi,

I don't even know if this is possible, but here goes.

I have 2 select queries.

1) SELECT w.Item AS [WareHouse Item], w.Quantity AS [WareHouse Quantity],

w.RestockLevel AS [WareHouse Restock], w.ReorderPoint AS [WareHouse ReorderPoint],

SUM(Wp.QuantityOrdered - Wp.QuantityReceivedToDate) AS WareHouseOnOrder

FROM WH.dbo.PurchaseOrderEntry AS wp INNER JOIN

WH.dbo.Item AS w ON wp.ItemID = w.ID INNER JOIN

WH.dbo.PurchaseOrder AS Wpo ON Wp.PurchaseOrderID = WPO.ID

Where (wPO.POType < 2) AND (wPO.Status = '0')

Group By w.Item, w.Quantity, w.RestockLevel, w.ReorderPoint

Order By w.Item

My socond query is the same, but from a different database on the same Server. If I join the 2 queries I get duplicate Items in my results, because of the 2 databases.

My question is:

Can I run both queries to create one View so that I can create my Crystal report from that?

Any better Ideas would be appretiated!

Thanks

what are you joining the two queries on?|||

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

|||

Arnie Rowland wrote:

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

or "UNION ALL" if you want the duplicates.

|||

Thanks Arnie and Michael

That was exactly what I needed.

Thanks!!!

|||

Hi,

I am not sure if view is your requirement or not. Aa far as the data from query to a report is concerned you can you use a temp table in stored procedure to do that or use UNION to combine these results.

Thanks,

Paraclete

|||

That is also VERY true.

After seeing that I can actually get all the information using the union that Arnie suggested I will probably create a stored procedure to run my select Statement.

Thanks Again!!