Showing posts with label intdeclare. Show all posts
Showing posts with label intdeclare. Show all posts

Monday, March 19, 2012

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 variables

Thanks for all the help, just one last question on xml, using the following
code
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
when running this query, how can i assign the reportType to @.rptType and
reportNumber to @.RptNo
Peter,
You can try:
DECLARE @.hDoc int
Declare @.RptType varchar(10)
Declare @.RptNo varchar(6)
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select @.RptType = reportType, @.RptNo = reportNumber
from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
... Of course, you won't be able to select the rest of the values along with
these variable assignments, so if you want a result set back you'll have you
re-select from OpenXML without the variables.
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:7627529A-C675-4DF8-AA27-78F44C66F5EB@.microsoft.com...
> Thanks for all the help, just one last question on xml, using the
following
> code
> DECLARE @.hDoc int
> Declare @.RptType varchar(10)
> Declare @.RptNo varchar(6)
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
> Select * from OpenXML(@.hDoc, '//Header') with
> (reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
> reportSequenceNumber varchar(6), userNumber varchar(6) )
> EXEC sp_xml_removedocument @.hDoc
> when running this query, how can i assign the reportType to @.rptType and
> reportNumber to @.RptNo