Conversion query

I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks

continuous variable prediction

Hi All,

I was wondering if there was a way to specify a range when training a model to predict continuous variables. For instance, the predicted variable can only have a range of 1 - 10.

Thanks

Not really, and it's possible that even if you restrict the training data to the range 1-10 you can end up with predictions outside that range (e.g. if you found x=2y and put 8 as your input).

What you can do is use VBA or Excel MIN/MAX functions on your prediction result. For example you can call

SELECT [MAX]( [MIN]( Predict(MyColumn), 10), 1)

FROM MyModel

PREDICTION JOIN ...

CONTAINSTABLE and local TABLE variables

Hi I have a stored procedure that uses local variables of type TABLE, is it possible to use CONTAINSTABLE on these as it appears to not be working correctly. The CONTAINSTABLE code is at the very bottom of this long piece of code.

CREATE PROCEDURE dbo.SearchJobs
@.STRING varchar(6000),
@.Longitude float,
@.Latitude float,
@.Distance int,
@.Category int
AS

DECLARE @.SEARCHTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50),
SearchColumn text)

DECLARE @.OUTPUTTABLE TABLE(JobId bigint,
Type varchar(20) PRIMARY KEY,
PromotionCode varchar(50),
Title varchar(50),
JobCategoryId int,
Description text,
ConditionOfEmployment text,
DollarAmount decimal,
DollarType varchar(20),
DateFrom datetime,
DateTo datetime,
ResumeEmail varchar(200),
Phone varchar(20),
AreaCode varchar(10),
AddressId bigint,
Street varchar(50),
Suburb varchar(50),
PostCode varchar(10),
State varchar(20),
Longitude float,
Latitude float,
Positions int,
EmployerId varchar(200),
Filled bit,
FilledBy varchar(200),
EmployerVisible bit,
EmployeeVisible bit,
AdditionalSearchString text,
Street2 varchar(50),
Suburb2 varchar(50),
PostCode2 varchar(10),
State2 varchar(20),
Longitude2 float,
Latitude2 float,
AddressId2 bigint,
Reference varchar(50))

DECLARE @.Cat as varchar(200)
IF (NOT @.Category is NULL)
BEGIN
SET @.Cat = 'AND JobCategoryId = ' + @.Category
END
ELSE
BEGIN
SET @.Cat = ''
END

-- This does not calculate exact coordinate distances. It is designed to find all jobs
-- within a certain distance of a reference point. It uses a sqare box for speed as
-- opposed to calculationg a cirecular reference.
IF (NOT @.Longitude is NULL)
BEGIN
EXEC (' INSERT INTO @.SEARCHTABLE SELECT JobId, Type, PromotionCode, Title, JobCategoryId, [Description], ConditionOfEmployment, DollarAmount, DollarType, DateFrom, DateTo, ResumeEmail,
Phone, AreaCode, AddressId, Street, Suburb, PostCode, State, Longitude, Latitude, Positions, EmployerId, Filled, FilledBy, EmployerVisible,
EmployeeVisible, AdditionalSearchString, Street2, Suburb2, PostCode2, State2, Longitude2, Latitude2, AddressId2, Reference, (str(Title) + '' '' + str(Suburb) + '' '' + str(Street) + '' '' + str(PostCode) + CAST([Description] AS varchar))
FROM Job
WHERE (Longitude < (@.Longitude + @.Distance)) AND (Longitude > (@.Longitude - @.Distance)) AND (Latitude < (@.Latitude + @.Distance)) AND (Latitude > (@.Latitude - @.Distance))' + @.Cat)

END
ELSE
BEGIN
EXEC('INSERT INTO @.SEARCHTABLE SELECT * FROM Job WHERE 1 ' + @.Cat)
END

IF(LEN(@.STRING) > 0)

BEGIN
DECLARE @.SearchString varchar(8000)
SET @.SearchString = ''
DECLARE @.INDEX INT
DECLARE @.SLICE nvarchar(4000)

SELECT @.INDEX = 1
DECLARE @.IDCounter int
SET @.IDCounter = 0
IF @.String IS NULL RETURN
WHILE @.INDEX !=0
BEGIN
SELECT @.INDEX = CHARINDEX(' ' ,@.STRING)
IF @.INDEX !=0
BEGIN
SELECT @.SLICE = LEFT(@.STRING,@.INDEX - 1)
END
ELSE
BEGIN
SELECT @.SLICE = @.STRING
--INSERT INTO @.SEARCHTERMS (searchterm) VALUES(@.SLICE)
IF @.SearchString = ''
BEGIN
SET @.SearchString = @.SLICE
END
ELSE
BEGIN
SET @.SearchString = @.SearchString + ' OR ' + @.SLICE
END


SET @.IDCounter = @.IDCounter + 1
SELECT @.STRING = RIGHT(@.STRING,LEN(@.STRING) - @.INDEX)
IF LEN(@.STRING) = 0 BREAK
END

END
END

EXEC sp_fulltext_table @.SEARCHTABLE

SELECT FT_TBL.JobId, FT_TBL.Type, FT_TBL.PromotionCode, FT_TBL.Title, FT_TBL.JobCategoryId, FT_TBL.[Description], FT_TBL.ConditionOfEmployment, FT_TBL.DollarAmount, FT_TBL.DollarType, FT_TBL.DateFrom, FT_TBL.DateTo, FT_TBL.ResumeEmail,
FT_TBL.Phone, FT_TBL.AreaCode, FT_TBL.AddressId, FT_TBL.Street, FT_TBL.Suburb, FT_TBL.PostCode, FT_TBL.State, FT_TBL.Longitude, FT_TBL.Latitude, FT_TBL.Positions, FT_TBL.EmployerId, FT_TBL.Filled, FT_TBL.FilledBy, FT_TBL.EmployerVisible,
FT_TBL.EmployeeVisible, FT_TBL.AdditionalSearchString, FT_TBL.Street2, FT_TBL.Suburb2, FT_TBL.PostCode2, FT_TBL.State2, FT_TBL.Longitude2, FT_TBL.Latitude2, FT_TBL.AddressId2, FT_TBL.Reference,
KEY_TBL.RANK
FROM @.SEARCHTABLE AS FT_TBL INNER JOIN
CONTAINSTABLE (@.SEARCHTABLE,SearchColumn,
@.SearchString

) AS KEY_TBL
ON FT_TBL.JobId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

The deprecated sp_fulltext_table operates on a table that exists in the database, see sp_fulltext_table (Transact-SQL) in Books Online.

Indexes cannot be created explicitly on table variables. See table (Transact-SQL) in Books Online.

Constant variables - round 2

Hi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>
No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
>

Constant variables

Hi!
Is there a way to create constants (i.e. constant variables) in stored
procedures? Basically I'm looking for the T-SQL counterpart of Oracle
PL/SQL's
"x_var constant integer := 999;"-type declarations.
Thx,
AgostonHi!
I'm forwarding this because I couldn't find another way to avoid
multiposting once I have forgotten to cross-post to other groups. Sorry,
anyway.
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi!
> Is there a way to create constants (i.e. constant variables) in stored
> procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> PL/SQL's
> "x_var constant integer := 999;"-type declarations.
> Thx,
> Agoston
>|||No. TSQL Programming doesnt have any keyword for constant variable.
but if you want to store constants, it is recommended to keep them in
separate table and read it in the TSQL block. one level of security you can
provide is not to allow anyone to update the constants table.
In sql world, constants are data values. like string literals, numeric and
decimal values.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:#Y6k8PA6EHA.2572@.tk2msftngp13.phx.gbl...
> Hi!
> I'm forwarding this because I couldn't find another way to avoid
> multiposting once I have forgotten to cross-post to other groups. Sorry,
> anyway.
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:ePy07CA6EHA.3472@.TK2MSFTNGP09.phx.gbl...
> > Hi!
> > Is there a way to create constants (i.e. constant variables) in stored
> > procedures? Basically I'm looking for the T-SQL counterpart of Oracle
> > PL/SQL's
> > "x_var constant integer := 999;"-type declarations.
> >
> > Thx,
> > Agoston
> >
> >
>