Another Question SP

Hi,
I have this I need to be able to update the information based on custid.
DECLARE @.sql1 Varchar(8000)
DECLARE @.custid varchar (10)
DECLARE @.custname varchar (80)
DECLARE @.doc varchar(8000)
SET @.custid='000900'
SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid='
+ @.custid Is this wrong?
SET @.custname = @.sql1
SET @.doc ='
<STORE>
<ADDRESS>
<CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
<CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variable
here based on the custid,
<ADDR_1>@.address1</ADDR_1>
<ADDR_2>@.address2</ADDR_2>
<ADDR_CITY>@.city</ADDR_CITY>
<ADDR_STATE>@.region</ADDR_STATE>
<ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
</ADDRESS>
When I run this I am getting NULL what is wrong with this?
Thanks
DibTry,
Select @.custname = c.custname From dbo.tblArCust c where c.custid = @.custid
...
AMB
"Dib" wrote:

> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where c.custid
='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a variab
le
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>
>|||Thanks, it worked. How can I handle NULL value here
Dib
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CF2BF93E-091C-494D-A625-19D36E041E9C@.microsoft.com...
> Try,
>
> Select @.custname = c.custname From dbo.tblArCust c where c.custid =
@.custid
> ...
>
> AMB
> "Dib" wrote:
>
c.custid='
variable|||Doesn't look like you have initialised @.sql1. Set it to a blank space and
try again.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have this I need to be able to update the information based on custid.
> DECLARE @.sql1 Varchar(8000)
> DECLARE @.custid varchar (10)
> DECLARE @.custname varchar (80)
> DECLARE @.doc varchar(8000)
> SET @.custid='000900'
> SET @.sql1 = @.sql1 + 'Select c.custname From dbo.tblArCust c where
> c.custid='
> + @.custid Is this wrong?
> SET @.custname = @.sql1
> SET @.doc ='
> <STORE>
> <ADDRESS>
> <CUST_ATTN>Mrssuchikanuganti</CUST_ATTN>
> <CUST_NAME>' + @.custname + '</CUST_NAME> I am trying to insert a
> variable
> here based on the custid,
> <ADDR_1>@.address1</ADDR_1>
> <ADDR_2>@.address2</ADDR_2>
> <ADDR_CITY>@.city</ADDR_CITY>
> <ADDR_STATE>@.region</ADDR_STATE>
> <ADDR_POSTAL>@.zipcode</ADDR_POSTAL>
> </ADDRESS>
> When I run this I am getting NULL what is wrong with this?
> Thanks
> Dib
>|||This is not working
This is what I did
SET @.custid = ''
SET @.custname = ''
SET @.address1 = ''
SET @.address2 = ''
SET @.city = ''
SET @.region = ''
SET @.zipcode = ''
SET @.doc =''
SET @.custid='000900'
SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End From
dbo.tblArCust where tblArCust.custid=@.custid)
SET @.doc ='
It worked fine at first when I changed the Custid to 00500 it returned NULL
now nothing works.
Thanks
Dib
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:uLcdeFWjFHA.708@.TK2MSFTNGP09.phx.gbl...
> Doesn't look like you have initialised @.sql1. Set it to a blank space and
> try again.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Dib" <dNOSPAMshahene@.conNOSPAMsoftware.com> wrote in message
> news:uZZHW4VjFHA.1196@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 20 Jul 2005 15:51:26 -0400, Dib wrote:

>This is not working
>This is what I did
>SET @.custid = ''
>SET @.custname = ''
>SET @.address1 = ''
>SET @.address2 = ''
>SET @.city = ''
>SET @.region = ''
>SET @.zipcode = ''
>SET @.doc =''
>
>SET @.custid='000900'
>SET @.custname = (Select case WHEN tblArCust.custname = NULL Then ''End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address1 = (Select case WHEN tblArCust.Addr1 = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.address2 = (Select case WHEN tblArCust.Addr2 = NULL Then ''END From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.city = (Select case WHEN tblArCust.City = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.region = (Select case WHEN tblArCust.Region = NULL Then '' End From
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.zipcode = (Select case WHEN tblArCust.PostalCode = NULL Then'' End Fro
m
>dbo.tblArCust where tblArCust.custid=@.custid)
>SET @.doc ='
>
>It worked fine at first when I changed the Custid to 00500 it returned NULL
>now nothing works.
>Thanks
>Dib
Hi Dib,
There are several problems with your code.
1. comparing to NULL with the = operator will allways fail - no value is
equal to NULL, not even NULL itself. (This is by design - NULL is
regarded as unknown values, so comparing NULL to NULL is like comparing
two unknown values; you can never say if two unknown values are equal or
unequal). Use IS NULL instead of = NULL for correct results.
2. The CASE statement lacks an ELSE part. If the WHEN part is not true
(which it never is, due to the reason above), and there is no ELSE part,
the result will default to NULL.
3. It is highly inefficient to execute the same query six consecutive
times. Instead, use the Transact-SQL extension to the SELECT statement
to assign values to all variables at once.
Here's some code to replace the snippet above. I also replaced the CASE
expressions with COALESCE expressions, that are (in this case)
equivalent. Oh, and since I use SELECT instead of SET, there's no need
to initialize the variables first - they'll always be assigned a value
in this statement!
SET @.custid = '000900'
SELECT @.custname = COALESCE(custname, ''),
@.address1 = COALESEC(Addr1, ''),
@.address2 = COALESCE(Addr2, ''),
@.city = COALESCE(City, ''),
@.region = COALESCE(Region, ''),
@.zipcode = COALESCE(PostalCode, '')
FROM dbo.tblArCust
WHERE custid = @.custid
(untested, since you didn't provide CREATE TABLE and INSERT statements)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)