problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIA
Change
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA
|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegro ups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.
sql

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

problem in an update??

Column is Varchar(100)
code was:
UDPATE productdesignprocessing
SET BOOKCOLLATION = '30,30,30,30,30'
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
why did I get an error NEAR = ?
I changed it with a @.var and it gagged on the code right after setting the
var
declare @.var varchar(100)
set @.var = '30,30,30,30,30'
UDPATE productdesignprocessing
SET BOOKCOLLATION = @.var
WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
I dtried to block it this way ['30,30,30,30,30'] but that didin't work
either?
Any ideas?
TIAChange
UDPATE to
UPDATE
__Stephen wrote:
> Column is Varchar(100)
> code was:
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = '30,30,30,30,30'
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> why did I get an error NEAR = ?
> I changed it with a @.var and it gagged on the code right after setting the
> var
> declare @.var varchar(100)
> set @.var = '30,30,30,30,30'
> UDPATE productdesignprocessing
> SET BOOKCOLLATION = @.var
> WHERE PRODUCTDESIGNCODE IN ('WAL-UCO','WAL-KSS')
> I dtried to block it this way ['30,30,30,30,30'] but that didin't work
> either?
> Any ideas?
> TIA|||"Ben" <bentennen@.gmail.com> wrote in message
news:1132254012.841826.36330@.g14g2000cwa.googlegroups.com...
> Change
> UDPATE to
> UPDATE
crawling under rock.....
Thanks.
Note to self. Must check spelling from developers before running their
scripts.

Problem in a dynamic stored procedurehelp

I create a stored procedure as following:
......

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50),
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)
AS

DECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'

if @.RowCount > 0
BEGIN

SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make

END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...

...............

This stored procedure work very well.

The question is how I add some dynamic condition inside the where condition.

I want to add:

If @.Model <> "See All Models"
AND a.Model = @.Model'

If @.AutoType <> "New/Used"
AND a.Condition = @.AutoType

I try several ways, but fail.

If you know how to add these two dynamic parameters into the condition of stored procedure, please help.Pass paremeters like this:

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)

and then do this in the Where clause:

a.Model = IsNull(@.Model,a.Model)

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||Hi douglas,

Thanks for answering my question. I think you misunderstand my question.
There are a lot of models for each Make. So if user select specific Make (like "Toyata") and "See All Model", then I only need add one condition "AND a.Make = @.Make" in my SP. But if user select specific Make and specific Model (like Tayata, Corolla), then in my where condition, I need add two condition " AND a.Make = @.Make AND a.Model = @.Model". So I need dynamic to add "AND a.Model = @.Model".

In your code, if I don't pass Model (Model=' ' or 'NULL') or Pass Model = "See All Model", then Model condition will become "AND a.Model = 'NULL' ", not any model will be retrieved.

In my situation, I have two or three this kind of dynamic conditions.

Any way to solve this problem?

Thanks again.

Lin|||The code I supplied will handle EXACTLY that situation. If parameter is NULL, then ALL will be selected. As I explained:

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||A small suggestion for the Original Poster is to d/l or use Books Online that's available for free download at Microsoft.com/Sql or in the Start Menu under Sql Server.

It's a great help doc that I have in my task bar as a quick link, and I use it frequently.

that's all. :) Enjoy your day.|||Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

Hi douglas,

Here is my SP, I have added @.model = NULL, and added IsNull in where condition as you said:

..............

CREATE Procedure Ruying_AutoSearch7
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.Miles float,
@.Zipcode varchar(5)
)
AS
DECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'
if @.RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND a.Model = IsNull(@.Model,a.Model)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO

...............

The following is my middle tier class funtion:

..................

Public Function GetAutoSearchItems7(ByVal Make As String, ByVal Model As String, ByVal Miles As Double, ByVal Zipcode As String) As SqlDataReader
 ' Create Instance of Connection and Command Object
 Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim myCommand As SqlCommand = New SqlCommand("Ruying_AutoSearch6", myConnection)
 ' Mark the Command as a SPROC
 myCommand.CommandType = CommandType.StoredProcedure
 ' Add Parameters to SPROC
 Dim parameterMake As SqlParameter = New SqlParameter("@.Make", SqlDbType.VarChar, 50)
 parameterMake.Value = Make
 myCommand.Parameters.Add(parameterMake)
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
 Dim parameterMiles As SqlParameter = New SqlParameter("@.Miles", SqlDbType.Float, 8)
 parameterMiles.Value = Miles
 myCommand.Parameters.Add(parameterMiles)
 Dim parameterZipcode As SqlParameter = New SqlParameter("@.Zipcode", SqlDbType.VarChar, 5)
 parameterZipcode.Value = Zipcode
 myCommand.Parameters.Add(parameterZipcode)
 ' Execute the command
 myConnection.Open()
 Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 ' Return the datareader result
 Return result
 End Function

.............

The folowing is ASP.NET code behind code:

....................

 SMake = Request.Form("ddlMake")
 SModel = Request.Form("ddlModel")
 lblMiles.Text = Request.Form("ddlMile")
 SMile = CInt(lblMiles.Text)
 lblZipcode.Text = Request.Form("txtZipcode")
 AutoCondition = Request.Form("rdlAutoCondition")
 lblMake.Text = SMake
 If SModel = "See All Models" Then
 lblModel.Text = ""
 Else
 lblModel.Text = SModel
 End If
 If AutoCondition = "New" Then
 lblAutoCondition.Text = "New"
 ElseIf AutoCondition = "Used" Then
 lblAutoCondition.Text = "Used"
 Else
 lblAutoCondition.Text = "New/Used"
 End If
 Dim mySearch As Ruying.SearchDB = New Ruying.SearchDB()
 dgSearchResult.DataSource = mySearch.GetAutoSearchItems7(SMake, lblModel.Text, SMile, lblZipcode.Text)
 dgSearchResult.DataBind()
 dgSearchResult.Dispose()

...............

The resule is that if I select "See All Model", nothing show up.
I change the code to:

 If SModel = "See All Models" Then
 lblModel.Text = "NULL"
 Else
 lblModel.Text = SModel
 End If

The result is same.

But if I select other model (specific model), there are data show up.

Anywhere I can change?

Thanks.

Lin|||You are confusing NULL with 'NULL'. NULL (no quotes) is a special word in SQL Server meaning no value, whereas 'NULL' is a 4 character string.

Do this:

If Model<>String.Empty AND Model<>"See All Models" then
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

This way, the parameter is NOT added if not set, and the default set will be NULL (as opposed to 'NULL', 'Show All Models' or '').|||Hi douglas,

I use

If Model<>String.Empty then
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

in the middle tier function. Now SP works very well. Thank you very much.

One more question in my program.

In asp page, I have AutoCondition to be selected. It has three values as "New", "Used" and "New/Used". In database, AutoCondition has "New", "Like New", "Good" and "Acceptable" values. Now I must use two stored procedure to retrieve the data. I use: AND a.Condition = IsNull(@.Condition, a.Condition) for user select "New" and "New/Used" (when select the "New/Used", set @.Condition = NULL, just like above case). I use: AND a.Condtion <> 'New' in second stored procedure for user select "Used".

Does any way can dynamic change this condition in WHERE clause and become just one stored procedure?

Thanks.

Lin|||I would create a table with the car conditions.

ConditionID int IDENTITY
Description nvarchar(100)
IsNew bit

Then, don't store the text, but store the COnditionID in the Auto table. Only "New" qualifies as new. So you would do a join, and have a condition like:

IsNew=IsNull(@.IsNew,IsNew)

Pass 1 for @.IsNew if they select New, 0 if they select Used, and NULL if they say New/Used.

Opon reflection, it is possible bit cannot be null, in which case you would need to use SmallInt (you should check this out).|||I'll try it. Thank you very much.sql

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

problem converting smalldatetime to 12hr time value

Hi,

I have two smalldatetime fields starttime and endtime

I want to display them like

select convert(varchar,starttime,108) + ' to ' + convert(varchar,endtime,108) from tbTest

the data in the field is

starttime endtime

1/3/2006 9:00:00 1/3/2006 6:00:00

the result I am getting is

09:00:00 to 18:00:00

Where as I want it something like this

9.00 AM to 6.00 PM

Is it possible? what query should be used for this?

Hi,

by taking a look to BOL ...

PRINT ' HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +

CONVERT(nvarchar(30),GETDATE(),109)

HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>M?r 9 2007 10:25:52:250AM

....

DECLARE @.ST SMALLDATETIME, @.ET SMALLDATETIME

SET @.ST = '1/3/2006 9:00:00'

SET @.ET ='1/3/2006 18:00:00'

SELECT (CONVERT(CHAR(11),@.ST,111) +

SUBSTRING(CONVERT(CHAR(19),@.ST,100),13,19) + ' To ' +

CONVERT(CHAR(11),@.ET,111) +

SUBSTRING(CONVERT(CHAR(19),@.ET,100),13,19))

-

2006/03/01 9:00AM To 2006/03/01 6:00PM

|||

What if i have an input like:

time

0910

1000

0530

0620

How do i convert this input into smalldatetime?

|||

Here is one option (uses the default date of [01/01/1900]):

Code Snippet

DECLARE @.MyTable table
( MyTime varchar(4) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( '0910' )
INSERT INTO @.MyTable VALUES ( '1000' )
INSERT INTO @.MyTable VALUES ( '0530' )
INSERT INTO @.MyTable VALUES ( '0620' )

SELECT MyTime = ( cast( 0 as smalldatetime ) + stuff( MyTime, 3, 0, ':' ))
FROM @.MyTable

problem converting float into nvarchar

I have a problem converting a float filed into nvarchar.
select cast(sold as varchar(50))
where sold=431597.15 results in 431597 and is ignoring always my decimals.
Do you have any idea how to fix this?
tyHi

The below works for me:
DECLARE @.Sold AS Float
SELECT @.Sold = 431597.15
SELECT CAST(@.Sold AS VarChar(50)) --Wrong
SELECT CAST(CAST(@.Sold AS Decimal(8, 2)) AS VarChar(50))--Correct

HTH