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 one text box value to the other text box in the sql reporting services Form

Hi,

Can some one help me in this case,

I am manipulating a value for onetext.value , And I want to assign like this

seconetext.value = onetext.value in the SQl reporting services form,

Is it possible? if so can any one help me with the syntax?

-Thanks

Yes, this is possible.

In "seconetext", use this expression:

=ReportItems!onetext.Value

-Chris

Assign multiple values using CASE in a Select Statement

Hello All,
I have a need to assign the values of the three variables in one select
statement. Currently, this is done using three
different Select statements :
Select @.Male = SMnemonic from sex where SName = 'Male'
Select @.Female = SMnemonic from sex where SName = 'Female'
Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
I would like to replace with just one Select statement. I thought that this
is simple, however, I'm getting vague results :
NULL
(1 row(s) affected)
NULL
(1 row(s) affected)
Unknown Mnemonic is : U
(1 row(s) affected)
Thanks,
Gopi
CREATE TABLE [dbo].[Sex] (
[SCode] [char] (10) ,
[SName] [varchar] (50),
[SMnemonic] [char] (10)
)
GO
Select * from Sex order by SCode
SCode SName SMnemonic
-- ---- --
1 Male
M
2 Female
F
3 Unknown
U
4 Not Known
NK
Select SMnemonic from sex where SName = 'Male'
Select SMnemonic from sex where SName = 'Female'
Select SMnemonic from sex where SName = 'Unknown'
Declare @.Male char(10)
Declare @.Female char(10)
Declare @.Unknown char(10)
Set @.Male = 'Junk'
Set @.Female = 'Junk'
Set @.Unknown = 'Junk'
Select @.Male = CASE SName
WHEN 'Male' THEN SMnemonic
END ,
@.Unknown = CASE SName
WHEN 'Unknown' THEN SMnemonic
END ,
@.Female = CASE SName
WHEN 'Female' THEN SMnemonic
END
from Sex
WHERE SName IN ('Male','Female','Unknown')
Select 'Female Mnemonic is : ' + @.Female
Select 'Male Mnemonic is : ' + @.Male
Select 'Unknown Mnemonic is : ' + @.UnknownTry,
Select
@.Male = case when SName = 'Male' then SMnemonic else @.Male end,
@.Female = case when SName = 'Female' then SMnemonic else @.Female end,
@.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
from
sex;
AMB
"rgn" wrote:

> Hello All,
> I have a need to assign the values of the three variables in one select
> statement. Currently, this is done using three
> different Select statements :
> Select @.Male = SMnemonic from sex where SName = 'Male'
> Select @.Female = SMnemonic from sex where SName = 'Female'
> Select @.Unknown = SMnemonic from sex where SName = 'Unknown'
> I would like to replace with just one Select statement. I thought that thi
s
> is simple, however, I'm getting vague results :
> --
> NULL
> (1 row(s) affected)
> --
> NULL
> (1 row(s) affected)
> --
> Unknown Mnemonic is : U
> (1 row(s) affected)
>
> Thanks,
> Gopi
> CREATE TABLE [dbo].[Sex] (
> [SCode] [char] (10) ,
> [SName] [varchar] (50),
> [SMnemonic] [char] (10)
> )
> GO
> Select * from Sex order by SCode
> SCode SName SMnemonic
> -- ---- --
> 1 Male
> M
> 2 Female
> F
> 3 Unknown
> U
> 4 Not Known
> NK
>
> Select SMnemonic from sex where SName = 'Male'
> Select SMnemonic from sex where SName = 'Female'
> Select SMnemonic from sex where SName = 'Unknown'
>
> Declare @.Male char(10)
> Declare @.Female char(10)
> Declare @.Unknown char(10)
> Set @.Male = 'Junk'
> Set @.Female = 'Junk'
> Set @.Unknown = 'Junk'
>
> Select @.Male = CASE SName
> WHEN 'Male' THEN SMnemonic
> END ,
> @.Unknown = CASE SName
> WHEN 'Unknown' THEN SMnemonic
> END ,
> @.Female = CASE SName
> WHEN 'Female' THEN SMnemonic
> END
> from Sex
> WHERE SName IN ('Male','Female','Unknown')
> Select 'Female Mnemonic is : ' + @.Female
> Select 'Male Mnemonic is : ' + @.Male
> Select 'Unknown Mnemonic is : ' + @.Unknown
>
>|||Alejadro,
Thanks a Million. I see the problem. Since ELSE part is missing it is
assigning NULLs and the reason why the last
variable, in this case Unknown, retains the value.
Gopi
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BB446702-10C6-47D3-91F7-3C6E9FD1D4FC@.microsoft.com...
> Try,
> Select
> @.Male = case when SName = 'Male' then SMnemonic else @.Male end,
> @.Female = case when SName = 'Female' then SMnemonic else @.Female end,
> @.Unknown = case when SName = 'Unknown' then SMnemonic else @.Unknown end
> from
> sex;
>
> AMB
> "rgn" wrote:
>

Asp.net - SQL Server remote connection falied

Hi, I have an issue with ASP.NET 2.0 and SQL Server 2005

My project when hosted in a machine its working, but in case of some other machine it is throwing the following error

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have turned off my firewall and network configuration for sql server and its also done as tcp/ip and named pipes…though it is not working …

The surprise is if I run using IDE its running , in the same machine after hosting it is not running, where the db is in another machine. What could be the problem? Any suggestions would be highly appreciated. Regards,Naveen

Regards,

Naveen

This is usually issue with user name, password and permissions.

First did you check your connection string? You need to change server name.

Second, are you using Windows authentication or mixed mode in your SQL Server?

|||

Thanks millet, it worked when I changed the permission

|||

Hi Naveen,

Can u pls. post the changes you made. because i'm also getting this error.