Attach a database

Yesterday, I found I could not do a select into in my development staging
database. I received the following message...
Server: Msg 9001, Level 21, State 3, Line 266
The log for database 'DB_ST' is not available.
Connection Broken
I found that there was no log file!!! I have no idea why not. So I decided
to try to detach the database and re-attach using sp_attach_single_file_db a
s
follows and received...
USE MASTER
EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
@.physname = 'D:\Databases\DB_ST_DATA.mdf'
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
Now I am stumped with no database and log. I have just called the
applications manager who thinks we may need to restore from another
environment. I question whether he backed up the development environment!!!!
!
Is this the only option I have?Yes. sp_attach_single_file_db might is only guaranteed to work if you detach
properly the database with sp_detach_db first, that is with the log file
still there. In other situation, like the one you have, it might work
sometimes, but is far from guaranteed. In that case you need to restore from
a backup.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
> Yesterday, I found I could not do a select into in my development staging
> database. I received the following message...
> Server: Msg 9001, Level 21, State 3, Line 266
> The log for database 'DB_ST' is not available.
> Connection Broken
> I found that there was no log file!!! I have no idea why not. So I decided
> to try to detach the database and re-attach using sp_attach_single_file_db
> as
> follows and received...
> USE MASTER
> EXEC sp_attach_single_file_db @.dbname = 'DB_ST',
> @.physname = 'D:\Databases\DB_ST_DATA.mdf'
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'D:\Databases\QUINN_ST_DATA.mdf' may be incorrect.
> Now I am stumped with no database and log. I have just called the
> applications manager who thinks we may need to restore from another
> environment. I question whether he backed up the development
> environment!!!!!
> Is this the only option I have?|||Thanks Jacco... I thought that would be the case.
"Jacco Schalkwijk" wrote:

> Yes. sp_attach_single_file_db might is only guaranteed to work if you deta
ch
> properly the database with sp_detach_db first, that is with the log file
> still there. In other situation, like the one you have, it might work
> sometimes, but is far from guaranteed. In that case you need to restore fr
om
> a backup.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:EDB0B871-2BFD-4720-9896-395234B36662@.microsoft.com...
>
>sql

AT the moment i have an SQL SELECT Statement as follows....

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id

order by date DESC

is it possible to join to another table in the same query to get a value out.

it would be JOIN categories C on H.CategoryID = C.CategoryID

is this possible. can anyone help?

This should help

http://www.vb-tips.com/InnerJoin.aspx

|||

is this what you want?

SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle

FROM HomePageImage H

JOIN shortlist SL on H.StoryId = SL.id JOIN categores C on h.CategoryID=C.CategoryID

order by H.date DESC

|||

Yes, Thanks for help.

asynchronous select statements

We have an application deployed on two seperate machines both reading from
the same database. The applications poll the database every few seconds and
find the first record in a table that has a bit field set to 0x0. The
application then updates the field to a value of 0x1 and then do some
processing based on the contents of the record. The problem we have is that
it is currently possible for each application to get the same record because
application 2 might select the record just before application 2 updates the
bit flag. we currently use two sql calls (in stored procs) such as:
select top 1 * from table1 where processed = 0x0
update table1 set processed = 0x1 where recid = @.ID
How can we avoid both apps getting the same recordHave the app call a single SP.
This SP updates the record, and then returns it to the client application.
This is in one transaction, so the other application can not get to the same
record.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:#GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
that
> it is currently possible for each application to get the same record
because
> application 2 might select the record just before application 2 updates
the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Jeremy,
This is how you would do it; put the desired row into lock until the update
is done. For rowlock to be effective, you need a primary key on the table!
ReadPast hint will allow you to bypass the locked row and process the next
available one. Without it, your #2 connection will have to wait until #1 is
done. You can try both scenarios out to gain some deeper insight.
e.g.
/*
--sample tb
create table t1(i int primary key, b bit)
insert t1 values(1,0)
insert t1 values(2,0)
insert t1 values(3,0)
*/
-- drop table t1
go
--on connection #1
--this will lock i=1
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
--on connnection #2
--this will lock i=2
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
> and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
> that
> it is currently possible for each application to get the same record
> because
> application 2 might select the record just before application 2 updates
> the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Magnificent! Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||Actually, testing discovered that this might not work, because if the sql
gets run at the same time, the select statements could select the same
record, because nothing is locked at that point.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||You could add another hint to the select to exclusively hold the lock. As
soon as the row is read, it's locked until you invoke commit/rollback.
e.g.
select *
from tb with (rowlock,xlock,readpast)
where pkid=@.para
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:uLpwUeIFFHA.464@.TK2MSFTNGP09.phx.gbl...
> Actually, testing discovered that this might not work, because if the sql
> gets run at the same time, the select statements could select the same
> record, because nothing is locked at that point.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> update
> is
> from
>

ASYNC_NETWORK_IO issue

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.

Thanks
-Ashis

Hi, Ashis,

Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.

Thanks,

Zhiqiang Feng

Asterisk in SQL

I'm trying to creat a search form and want to use the Like clause in my select statement so that a user can enter part of a word rather than the entire word. When I use this sql I get no results:

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '*ARR*'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

The problem is with the * . If I remove the * it works fine. If I use the code within Access rather than from my aspx code, it works fine. Is there a work around for this?

Hi,

% (percent) is the standard wildcard character. Access client does support *, but when you use it via OleDB (Jet provider) it also requires %.

Therefore put

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '%ARR%'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

|||That did it. Thanks!!!

asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)

SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER

Regards,

Fernando

|||

It sude did!
Thanks!

assistance with sql query

Thanks in advance, I am trying to display the count of uptimes and downtimes in a single query. i started with something like
SELECT DISTINCT servername,
(SELECT COUNT(*)
FROM pingtable
WHERE (status = '0')) AS Uptime,
(SELECT COUNT(*)
FROM pingtable
WHERE (status <> '0')) AS DownTime
FROM pingtable

but this gives me the
server1 7 2
server1 7 2
...

Table layout and data:
servername status

server1 up
server1 up
server1 down
server2 up
server2 up
server2 up
server3 down
server3 up
server3 up

the output I would like to have is

Server UpCount DownCount
Server1 2 1
Server2 3 0
Server3 2 1Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||Thanks so much for holding my hand there,
Here is the final query that worked perfectly
Select Server,
sum(Case Status when 0 then 1 else 0 end) as Uptime,
sum(Case Status when 0 then 0 else 1 end) as Downtime
From eladmin.PingstatsNT
Group by Server

Originally posted by blindman
Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||FYI, if your Status field always holds zeros or ones, set it's data type to bit to ensure that your code will always work correctly.

blindman

Assistance with error message

I am trying to run this script in Query Analyser and it seems to have a
problem in line 3:
-
select ID, 9, '2004', (select id from type where type = 'MF') from Dept
where dept in ('Internal Medicine', 'Surgery')
and ID + (select id from type where type = 'MF') +
convert(varchar(2), 9) +
convert(varchar(4), '2004')
not in
(select m.deptID + m.typeid + convert(varchar(2), m.dtmonth) +
convert(varchar(4), m.dtyear) from monthdept m, dept d, type t
where m.DeptID *= d.ID
and m.TypeID *= t.Type
)
The error message is: Invalid operator for data type. Operator equals add,
type equals uniqueidentifier.
I have tried playing with CAST/CONVERT without success. I am trying to look
for records on a key that spans 4 fields. Any suggestions/thoughts?
Schoo
PS: SQL Server 2000 running on W2K Server
Hi Schoo,
From your descriptions, I understood that the following codes raise an
error in QA, correct me if I was wrong, however, when I copied codes you
provided to my QA and then Parse Query (Ctrl + F5), it passed. To make
further research on this issue, I would appreciated if you could provide me
detailed DDL of your database and some sample record for me to reporduce it
on my machine. One more question, have you upgraded to the latest SQL
Server Service Pack?
Here is the document on how to get DDL from Enterprise Manager
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.
|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
Anith
|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>
|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning variables with SELECT statements

Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.Try:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
Hi,
The syntax
SELECT @.varname = colname FROM table WHERE ...
is valid in SQL server, but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ...
which would be useful if (for example) getting the most recent index number
from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
I can use a workaround such as
SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
As I have a workaround that works well, I'm not too concerned about this -
just wondering if I'm missing something with the syntax that causes my
second example to fail.
John.|||>> ...but I am unable to use the syntax
SELECT @.varname = TOP 1 colname FROM table WHERE ... <<
The variable should be immediately before the column name like:
SELECT TOP 1 @.varname = colname FROM table ...
--
Anith|||John,
You were so close...
SELECT TOP 1 @.varname = colname FROM table WHERE ...
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||How about this method:
SELECT TOP 1 @.varname = colname FROM table WHERE ...
--
Keith
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:%23yfS71clEHA.3372@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The syntax
> SELECT @.varname = colname FROM table WHERE ...
> is valid in SQL server, but I am unable to use the syntax
> SELECT @.varname = TOP 1 colname FROM table WHERE ...
> which would be useful if (for example) getting the most recent index
number
> from a table using an ORDER BY clause (e.g. ORDER BY entry_date DESC)
> I can use a workaround such as
> SET @.varname = (SELECT TOP 1 colname FROM table WHERE ...)
> As I have a workaround that works well, I'm not too concerned about this -
> just wondering if I'm missing something with the syntax that causes my
> second example to fail.
> John.
>|||Keith Kratochvil wrote:
> How about this method:
> SELECT TOP 1 @.varname = colname FROM table WHERE ...
Thanks all - much appreciated.
John.

Assigning Variables

i have a snippit of a query
DECLARE @.INPUTRPT int
DECLARE @.ADDACSRPT int
SELECT a.companyname,
CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
from INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'INPUTRPT',
CASE WHEN EXISTS (Select Count(Licence)
from ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
THEN (Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence =
a.licence))
ELSE 0
END as 'ADDACSRPT'
how can i assign the variable to the case results"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B4146EC0-BA94-44BB-B7C0-AABBED189C77@.microsoft.com...

> how can i assign the variable to the case results
SELECT @.variable = CASE Column1
WHEN 1 THEN 'Hello'
ELSE 'World'
END AS SomeName
FROM...
Rick Sawtell
MCT, MCSD, MCDBA|||On Wed, 14 Dec 2005 05:50:24 -0800, Peter Newman wrote:

>i have a snippit of a query
>DECLARE @.INPUTRPT int
>DECLARE @.ADDACSRPT int
>SELECT a.companyname,
> CASE WHEN EXISTS (Select @.INPUTRPT = Count(Licence)
> from INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.INPUT_HEADERS as b
> WHERE (b.DatePostedToBureau IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'INPUTRPT',
> CASE WHEN EXISTS (Select Count(Licence)
> from ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> THEN (Select Count(Licence)
> from BossData.dbo.ADDACS_HEADERS as b
> WHERE (b.DateSubmitted IS NULL AND b.licence =
>a.licence))
> ELSE 0
> END as 'ADDACSRPT'
>how can i assign the variable to the case results
Hi Peter,
Rick already answered the final question, but I believe that the query
can be simplified - you don;t need the CASE expressions (a COUNT
subquery always returns one row, so the EXISTS test will always result
in True).
SELECT a.companyname,
(Select Count(Licence)
from BossData.dbo.INPUT_HEADERS as b
WHERE (b.DatePostedToBureau IS NULL AND b.licence = a.licence))
as 'INPUTRPT',
(Select Count(Licence)
from BossData.dbo.ADDACS_HEADERS as b
WHERE (b.DateSubmitted IS NULL AND b.licence = a.licence))
as 'ADDACSRPT'
(Later)
I just noticed that you try to assign a variable in a statement that
will also return rows to the client. That is not possible in SQL Server.
You either assign variables, OR you return data - never both.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Assigning the results of a table function to a table variable

SQL Server 2K SP1.
I'm trying to put the results of a Table function into a Table variable for
use in a Select. My code is:
DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
SELECT LoanNo
FROM UV_Tran016 AS t16
INNER JOIN @.LoanTable lt
ON t16.LoanNo = lt.LoanNo
The error I get is:
Server: Msg 195, Level 15, State 10, Line 4
'UDF_GetLoansInDeal' is not a recognized function name.
Line 4 is the SET statement.
SELECT TOP 10 *
FROM UDF_GetLoansInDeal (1, 8)
works just fine.
The function is defined as:
CREATE FUNCTION UDF_GetLoansInDeal
(
@.DealType int
, @.Id int
)
RETURNS @.LoansTable table
(
LoanNo DECIMAL(10,0)
)
AS
BEGIN
IF @.DealType = 1
BEGIN
INSERT @.LoansTable
SELECT Loan_No
FROM U_PledgeData
WHERE PledgeId = @.Id
ORDER BY PledgeId
END
ELSE
IF @.DealType = 2
BEGIN
INSERT @.LoansTable
SELECT Loan_No
FROM U_IntercompanyData
WHERE IntercompanyId = @.Id
ORDER BY IntercompanyId
END
RETURN
END
What am I doing wrong?
Thanks!I'm not positive, and obviously this is untested, but I think you just meant
to do this:
SELECT t16.LoanNo
FROM UV_Tran016 AS t16
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t16.LoanNo = lt.LoanNo
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>|||SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
should be
INSERT INTO @.LoanTable
SELECT * FROM dbo.UDF_GetLoansInDeal (1, 8)
Keith Kratochvil
"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>|||Aaron,
Ah, I see. Since the function returns a table, I can use the function in the
place of a table name in the join.
That makes sense, but I wanted to assign the table to a table variable
because I'm really going to do a delete on multiple tables and didn't want t
o
execute the function on each delete. For example,
DELETE UV_Tran016 AS t16
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t16.LoanNo = lt.LoanNo
DELETE UV_Tran022 AS t22
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t22.LoanNo = lt.LoanNo
DELETE UV_Tran025 AS t25
INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
ON t25.LoanNo = lt.LoanNo
etc.
I believe the next reply shows me how to do that.
Thanks for the help,
Bob
"Aaron Bertrand [SQL Server MVP]" wrote:

> I'm not positive, and obviously this is untested, but I think you just mea
nt
> to do this:
> SELECT t16.LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN dbo.UDF_GetLoansInDeal(1,8) lt
> ON t16.LoanNo = lt.LoanNo|||Keith,
Ah, this is another place where SET is not used to assign a value to a local
variable. I get it.
Thanks,
Bob
"Keith Kratochvil" wrote:

> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> should be
> INSERT INTO @.LoanTable
> SELECT * FROM dbo.UDF_GetLoansInDeal (1, 8)
> --
> Keith Kratochvil|||"Bob" <notrainsley@.worldsavings.com> wrote in message
news:DD09EA8C-6F54-4086-BAF7-4DD2CC42879F@.microsoft.com...
> SQL Server 2K SP1.
> I'm trying to put the results of a Table function into a Table variable
> for
> use in a Select. My code is:
> DECLARE @.LoanTable table (LoanNo DECIMAL(10,0), PRIMARY KEY (LoanNo))
> SET @.LoanTable = UDF_GetLoansInDeal (1, 8)
> SELECT LoanNo
> FROM UV_Tran016 AS t16
> INNER JOIN @.LoanTable lt
> ON t16.LoanNo = lt.LoanNo
> The error I get is:
> Server: Msg 195, Level 15, State 10, Line 4
> 'UDF_GetLoansInDeal' is not a recognized function name.
> Line 4 is the SET statement.
> SELECT TOP 10 *
> FROM UDF_GetLoansInDeal (1, 8)
> works just fine.
> The function is defined as:
> CREATE FUNCTION UDF_GetLoansInDeal
> (
> @.DealType int
> , @.Id int
> )
> RETURNS @.LoansTable table
> (
> LoanNo DECIMAL(10,0)
> )
> AS
> BEGIN
> IF @.DealType = 1
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_PledgeData
> WHERE PledgeId = @.Id
> ORDER BY PledgeId
> END
> ELSE
> IF @.DealType = 2
> BEGIN
> INSERT @.LoansTable
> SELECT Loan_No
> FROM U_IntercompanyData
> WHERE IntercompanyId = @.Id
> ORDER BY IntercompanyId
> END
> RETURN
> END
> What am I doing wrong?
> Thanks!
>
The result of a table-valued function is a result set. It can't be assigned
directly to a variable. Replace the SET with an INSERT:
INSERT INTO @.LoanTable (LoanNo)
SELECT LoanNo FROM UDF_GetLoansInDeal (1, 8);
Another change you should make is to remove the ORDER BYs in your function.
They do nothing except maybe slow down the query.
Usually it's better to use in-line table-valued functions in preference to
multi-statement ones where you can. For example your function could be
rewritten as follows, which may yield a better query plan and faster
results.
CREATE FUNCTION UDF_GetLoansInDeal
(
@.DealType int
, @.Id int
)
RETURNS TABLE
AS
RETURN
(
SELECT Loan_No AS LoanNo
FROM U_PledgeData
WHERE PledgeId = @.Id
AND @.DealType = 1
UNION ALL
SELECT Loan_No AS LoanNo
FROM U_IntercompanyData
WHERE IntercompanyId = @.Id
AND @.DealType = 2
)
GO
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

assigning Select results to local vars in SP

Hi. I'd like to assign the results of a select statement to a local
variables in my stored procedure. My intent is something like this:
SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
only, some how I'd like to get the field2,field3 into variables. Can
this be done?
Thanks in advanceFirst of all, do not use TOP without using ORDER BY, unless selecting
somewhat random results is required (which I gues is not).
Other than that, this is the way to go:
select @.variable_name = owner.table.colum
from owner.table
where (owner.table.another_column = @.parameter)
Don't forget to look up using local variables in Books Online.
ML|||Johnny,
Something like this:
USE Pubs
GO
CREATE PROC TESTPROC
@.AID varchar(11)
AS
DECLARE @.FName varchar(30)
DECLARE @.LName varchar(30)
SELECT @.FName = au_fname, @.LName = au_lname
FROM authors
WHERE au_id = @.AID
PRINT @.FName + ' ' + @.LName
GO
EXEC TESTPROC '172-32-1176'
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127950731.353336.297780@.g43g2000cwa.googlegroups.com...
> Hi. I'd like to assign the results of a select statement to a local
> variables in my stored procedure. My intent is something like this:
> SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
> only, some how I'd like to get the field2,field3 into variables. Can
> this be done?
> Thanks in advance
>|||Thanks Jerry, I'll try this out!|||Just be careful! If the SELECT returns more than 1 rows, you will *not* get
an error. The
variable(s) will contain the value for an unspecified row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127953579.251983.177820@.o13g2000cwo.googlegroups.com...
> Thanks Jerry, I'll try this out!
>

Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

Assigning results of a select query to variables...

Hi,

I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.

So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@.idinput"

OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?

This is what I've got so far...

[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()

dbcon.Close()
[/code]

The main prob is just what to connect this record to in order to access the individual fields.

Thx :)Return it as a datereader, then:

Do while dbreader.read()
var1 = dbreader("field1")
var2 = dbreader("field2")...
Loop

You could also return the values as output parameters. This used to be the much faster way in ADO, but I've read that performance is about the same either way in ADO.net. If it matters that much, try both and test it.|||Thx man, that does just the job. :)

Assigning permissions to a table

I have a table that is being dropped and then recreated by "SELECT INTO"
through a scheduled stored procedure. So, therefore, all permissions are
being dropped as well.
I need to re-establish SELECT privileges for this table to a role containing
a user, or if it can't, a user alone. I have a role assigned to a specific
set of users who would need to SELECT this table. The stored procedure takes
care of all UPDATES/INSERTs so therefore doesn't need this.
JulianDo:
GRANT SELECT ON tbl TO <role>
Anith|||Looku GRANT in BOL
GRANT SELECT
ON authors
TO public
GO
GRANT SELECT
ON authors
TO Mary, John, Tom
GO
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/

Assigning column types in select into

Group,

Is there a way to assign nullability on a column when using a select into?
I've tried some of the usual things like coalsce, isnull, and cast. Since
the new table gets definition from the source table or can be somewhat
adjusted with cast is there a way to cast a not null? In the example below
how can I select into causing tableone_new..col2 to be not null. We
typically must use an alter statement after the select into but this seems
inefficient.

Thanks,
Ray

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
col2
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
goRay (someone@.nowhere.com) writes:
> Is there a way to assign nullability on a column when using a select
> into? I've tried some of the usual things like coalsce, isnull, and
> cast. Since the new table gets definition from the source table or can
> be somewhat adjusted with cast is there a way to cast a not null? In
> the example below how can I select into causing tableone_new..col2 to be
> not null. We typically must use an alter statement after the select
> into but this seems inefficient.

As far as I know there isn't. You might be better off with CREATE TABLE
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Ray" <someone@.nowhere.com> wrote in message
news:t7tTd.8720$rK1.8198@.newssvr31.news.prodigy.co m...
> Group,
> Is there a way to assign nullability on a column when using a select into?
> I've tried some of the usual things like coalsce, isnull, and cast. Since
> the new table gets definition from the source table or can be somewhat
> adjusted with cast is there a way to cast a not null? In the example
> below how can I select into causing tableone_new..col2 to be not null. We
> typically must use an alter statement after the select into but this seems
> inefficient.

<snip
ISNULL() appears to do what you want (8.00.760 Enterprise Edition), although
COALESCE() does not (don't ask me why). Personally, I agree with Erland - it
would be much better to use CREATE TABLE and therefore make your intentions
clear and your data model explicit.

Simon

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
isnull(col2, 1) as col2 -- tableone_new.col2 is NOT NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
coalesce(col2, 1) as col2 -- tableone_new.col2 IS NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go|||Simon Hayes (sql@.hayes.ch) writes:
> ISNULL() appears to do what you want (8.00.760 Enterprise Edition),
> although COALESCE() does not (don't ask me why).

There is a similar case with indexed views where you can use isnull()
to make a view indexable but where coalesce() does not cut it. And,
no, I can't really make any sense of it.

(And in the particular case where I looked at it, the view does not
index at all in SQL 2000, but does so in SQL 2005 when you use isnull.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Assign XML to variable

How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?

I know that the following code works....

select * from tblUsers where userId = 1225 for XML raw

It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."

declare @.strXml nvarchar(1000)

set @.strXml = (select * from tblUsers where userId = 1225 for XML raw)

Ideas?

It is not possible to do this in SQL Server 2000 using TSQL. You cannot consume the XML output on the server-side in any form. This is possible in SQL Server 2005 since there is a native XML data type and there are lot of improvements to FOR XML clause.

assign variable value in Exists subquery

Hi
How to assign a variable value from if exist ?
like
declare @.i int
if exits( select @.i = ID from table1 where ID = 100)
-- do sth
but I always get an error
Thanks a lot for helpingAnn
You cannot do in that way.
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||Ann,
Posting the actual error would help.
Does it have to be in a subquery?
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
is easy to read/follow.
Regards
AJ
"Ann" <Ann@.discussions.microsoft.com> wrote in message news:14A79DE3-EC8D-45D9-8554-F96AF32
956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||declare @.i int
SET @.i = ( select ID from table1 where ID = 100)
if @.i IS NOT NULL ......
Although I assume you can do whatever you want to do probably simpler with a
join instead of an IF, but for that you would have to post the rest of your
code.
Jacco Schalkwijk
SQL Server MVP
"Ann" <Ann@.discussions.microsoft.com> wrote in message
news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
> Hi
> How to assign a variable value from if exist ?
> like
> declare @.i int
> if exits( select @.i = ID from table1 where ID = 100)
> -- do sth
> but I always get an error
> Thanks a lot for helping|||My problem is
I have two tables
Product
Product_ID Product_Name
100 Apple
101 Peach
102 Banana
Order
Product_ID Customer_ID Quantity
100 1 5
101 1 6
Now I need to generate a report with every product and every customer. The
problem is that if nobody purchases Banana(which is 102),I need to insert
null
so it will look like
Customer_ID Product_ID Quantity
1 100 5
1 101 6
1 102 NULL
IF EXISTS(
SELECT * FROM Order WHERE Customer_ID =1) INSERT INTO
#temp(Customer_ID ,Product_ID , Quantity) SELECT Customer_ID ,Product_ID,
Quantity FROM Order WHERE Customer_ID = 1
ELSE
INSERT INTO #temp(Customer_ID ,Product_ID ,
Quantity) VALUES(1,Product_ID,NULL) -- suppose only one product here
If I use
declare @.i int -- Defaults to NULL
select @.i = ID from table1 where ID = 100
if @.i is not null
-- do sth
I won't get 102(banana) in here
If I user
DECLARE @.ord INT
IF EXISTS (SELECT * FROM Orders WHERE OrderId=10249)
SELECT @.ord=Orderid FROM Orders WHERE OrderId=10249
SELECT @.ord
I'd have to select twice,that's why I am asking if possible,I can assign a
value in if exists
Thanks everyone
"Jacco Schalkwijk" wrote:

> declare @.i int
> SET @.i = ( select ID from table1 where ID = 100)
> if @.i IS NOT NULL ......
> Although I assume you can do whatever you want to do probably simpler with
a
> join instead of an IF, but for that you would have to post the rest of you
r
> code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Ann" <Ann@.discussions.microsoft.com> wrote in message
> news:14A79DE3-EC8D-45D9-8554-F96AF32956DE@.microsoft.com...
>
>|||I assume you have a Customers table as well? Try:
SELECT C.customer_id, P.product_id,
COALESCE(SUM(quantity),0) AS quantity
FROM Customers AS C
CROSS JOIN Products AS P
LEFT JOIN Orders AS O
ON C.customer_id = O.customer_id
AND P.product_id = O.product_id
AND O.orderid = 10249
GROUP BY C.customer_id, P.product_id
I would think that the Orders table is denormalized if it has both the
order number and the customer id. Doesn't Order determine Customer?
David Portas
SQL Server MVP
--|||Thanks a lot,that 's what I need
"David Portas" wrote:

> I assume you have a Customers table as well? Try:
> SELECT C.customer_id, P.product_id,
> COALESCE(SUM(quantity),0) AS quantity
> FROM Customers AS C
> CROSS JOIN Products AS P
> LEFT JOIN Orders AS O
> ON C.customer_id = O.customer_id
> AND P.product_id = O.product_id
> AND O.orderid = 10249
> GROUP BY C.customer_id, P.product_id
> I would think that the Orders table is denormalized if it has both the
> order number and the customer id. Doesn't Order determine Customer?
> --
> David Portas
> SQL Server MVP
> --
>