problem with IN (@intParameter) in a stored procedure

I want to pass a list of integer values as a parameter for a stored procedure where the criteria column is an integer

I understand what the problem is but I cannot seem to find a solution, I can't pass it as a varchar because the column is an int and I can't pass it as an in because of the commas in the list

SELECTAppId, ApplicationName, NextPage, AppData, GrpLogo, DeptLogin
FROMdbo.TblApplication
WHERE(AppIdIN(@.intParameter))
ORDER BYSortOrder, ApplicationName

any help please

This question has been answered a few times, with sample code in the solution. Please search in these forums. I am sure you will find it.|||Search for the Split function.|||

thanks for that I found304221 Terri Morton's bit did the trick

|||

I did as you suggested and found 304221 from Terri Morton's did just what I wanted

Many Thanks

Problem with getdate function in optional parameters

Hi, I want to write a StoredProcedure with one optional input parameter of Date and when it is missing I want current date to be used.

I have written the following StoredProcedure, but getdate function doesn`t work. If I replace it with a constant date, it works.

ALTER PROCEDURE

[dbo].[LinksFees_Record]

@.Date

datetime=getdate

AS

INSERT INTOLinkSearchFees

(LinkID, Price, [Date])

SELECTIDASLinkID, SearchDayFeeASPrice, @.DateFROMLinksWHERE(SearchDayFee > 0)

RETURNWhen I call the StoredProcedure the following exception occur:Conversion failed when converting datetime from character string.

How can I fix it?

Hi!

Try this:

ALTER PROCEDURE

[dbo].[LinksFees_Record]
@.Date

datetime = NULL
AS

IF @.DATE IS NULL SET @.Date= getdate()
... rest of your procedure goes here ...

Now if the users passes no parameter then @.Date will be replaced by getdate() result. Beware that if the user passes a NULL it will also be replaced by GetDate() results.

Have a good day,

David

problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with format of DateTime parameter

I am in Australia.
Our machines have an Australian/English locale which as a date order of
Day/Month/Year.
I am using SQL RS 2005.
When I add a parameter of type DateTime and then preview the report a
strange thing happens.
* Use an Australian machine locale
* Create a dataset with no SQL. The dataset is just
SELECT @.MyDateParm
* Create a simple layout that uses this dataset
* Preview the report. You should get the nice date selection dialog.
* Select 1st December 2005
* View the report - It switches magically to 12th January 2005
* View again - it switches back etc..
Now what is going on here?
(Professor Julius Sumner Miller)
(I know my likely solution is to move to the USA - or at least change
my locale :)Has anyone ever encountered this problem?
I think it is a bug in RS2005.
Does anyone know how I could report this?
Thanks
RBot wrote:
> I am in Australia.
> Our machines have an Australian/English locale which as a date order of
> Day/Month/Year.
> I am using SQL RS 2005.
> When I add a parameter of type DateTime and then preview the report a
> strange thing happens.
> * Use an Australian machine locale
> * Create a dataset with no SQL. The dataset is just
> SELECT @.MyDateParm
> * Create a simple layout that uses this dataset
> * Preview the report. You should get the nice date selection dialog.
> * Select 1st December 2005
> * View the report - It switches magically to 12th January 2005
> * View again - it switches back etc..
> Now what is going on here?
> (Professor Julius Sumner Miller)
> (I know my likely solution is to move to the USA - or at least change
> my locale :)|||Well there are other people having problems with Date type parameters:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=150&messageid=286639
but where should I report this so it can be fixed?

Problem with Dynamic Where Clause

I have a stored proc that accepts a varchar as a parameter. What is being
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>

Problem with dynamic sql statement

Can someone tell me why this is not executing properly. No error
messages, just no rows returned. I need to have an output parameter
and a return value.
Thanks in advance
Julie Barnet
CREATE PROCEDURE dbo.sel_LookupChar
(
@.Lookup_Value NVarChar(30),
@.Lookup_Field NVarChar(30),
@.Lookup_Table NVarChar(30),
@.MyOutput nVarChar(100) OUTPUT
)
AS
Declare @.SqlStr VarChar(1000)
Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
From " + @.Lookup_Table + " Where " + @.Lookup_Field
Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
Exec(@.SqlStr)
return @.@.rowcount
GOUse ' not " for string delimiters.
Also, try PRINT @.SqlStr instead of EXEC, and show us the result.
"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0308270858.4563cc29@.posting.google.com...
> Can someone tell me why this is not executing properly. No error
> messages, just no rows returned. I need to have an output parameter
> and a return value.
> Thanks in advance
> Julie Barnet
> CREATE PROCEDURE dbo.sel_LookupChar
> (
> @.Lookup_Value NVarChar(30),
> @.Lookup_Field NVarChar(30),
> @.Lookup_Table NVarChar(30),
> @.MyOutput nVarChar(100) OUTPUT
> )
> AS
> Declare @.SqlStr VarChar(1000)
> Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
> From " + @.Lookup_Table + " Where " + @.Lookup_Field
> Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
>
> Exec(@.SqlStr)
> return @.@.rowcount
> GO

Problem with Dynamic SQL !

Hi friends,

I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes...

---------------------------------------
CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
EXECUTE( 'SELECT '+ @.nv +' = id from ' + @.seqName )
SET @.nextVal = @.nv
END

---------------------------------------
after i created the table & procedure, i executed the below code:
---------------------------------------
DECLARE @.nextVal1 int
EXECUTE NextVal 'seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1
---------------------------------------

but it says

Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
Line 1: Incorrect syntax near '='.
(return status = 0)

Can anyone point out where i went wrong?

JakeDECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||Hi Eniqma,

It didn't solve my prob. same error...
The problem is in the select statement
EXECUTE( 'SELECT '+ @.nv +' = nextVal from ' + @.seqName )
It says Incorrect syntax near '='.

any idea?

Jake|||Oops ... i forgot ... you cannot create a dynamic string inside an execute statement

You will have to do something like

create procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )
select @.query ='SELECT '+ @.nv +' = id from ' + @.seqName
EXECUTE(@.query )
SET @.nextVal = @.nv
END|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= @.@.identity

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1|||alter procedure NextVal (@.seqName varchar(20),@.nextval int output)
AS
BEGIN
DECLARE @.nv int,@.query varchar (300)
select @.query = 'DELETE from ' + @.seqName
EXECUTE(@.query )
select @.query = 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)'
EXECUTE(@.query )

select @.nextval= scope_identity()

END

DECLARE @.nextVal1 int
EXECUTE NextVal @.seqName='seqtest', @.nextVal = @.nextVal1 OUTPUT
print @.nextVal1



You should really be using scope_identity()

And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...|||Hi eniqma, when the procedure is executed, it will say

Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
Syntax error converting the varchar value 'SELECT ' to a column of data type int.

as int is concatenated with string.

@.@.IDENTITY approach guides me to the solution. but i didn't use @.@.IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.

so here is the final procedure

---------------------------------------
alter procedure NextVal (@.seqName varchar(20), @.nextVal int OUTPUT)
AS
BEGIN
EXECUTE( 'DELETE from ' + @.seqName)
EXECUTE( 'INSERT INTO ' + @.seqName + '(dummyCol) VALUES(0)' )
SET @.nextVal = IDENT_CURRENT(@.seqName)
END
---------------------------------------

thanks eniqma & harshal for your time & help.

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake|||hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

any clue why it didn't work?

Jake

I think u have already answered the question.

But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

since the select and insert are not in the scope i think it wont work.|||Hmm ... you are right harshal ... never thought about that

Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...|||i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...

thanks for the clarification, harshal.

Jake|||oh! sorry, i missed that... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
Thanks for your interest.

Jake|||Oh ... so thats what you are trying to do ...

Talking about scope ... its the same in sql as in other languages ...
If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.|||now i understand, enigma... glad to see your reply.

Thanks,
Jake|||Without reading this little lot too deeply, the answer to the original question is:
1: Look at the spaces before and after you + signs '+ @.n +' needs to be:
' + @.n + '
2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.

E.G. 'I am a varchar ' + CAST(@.IntVariable AS Varchar(250)) + ' The rest of the varchar string'

Otherwise you get the converting int to varchar error.

Have fun
Best regards
Steve

P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@.SqlString as Varchar(250)) to check your Sequel statement for errors.

Problem with Dynamic SQL

I am using Dynamic SQL in stored procedures. This following procedure accepts a parameter @.filter which provides the WHERE clause for the query if there is one. Here is the code:

CREATE PROCEDURE TC3_GetAllJobOrders
(
@.filter NVARCHAR(500)
)
 AS
DECLARE @.sqlString NVARCHAR(500)
IF @.filter IS NOT NULL
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder WHERE @.filter'
EXECUTE sp_executesql @.sqlString, N'@.filter NVARCHAR(500)', @.filter
END
ELSE
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder'
EXECUTE sp_executesql @.sqlString
END
GO

When I pass in null for @.filter, it works fine and returns all rows in the TC3_JobOrder table. However when I pass in a syntactically correct filter I get the following error:

"Line 1: Incorrect syntax near '@.filter'."

Even a trivial filter like "1 = 1" gives me this error. I have changed the query from a SELECT statement to a RAISERROR statement so that it prints out the SQL after the value for @.filter has been injected, and everything is syntactically correct. Any ideas why it would be giving me that error? It all seems right...

Jason PachecoYou can't pass in the WHERE condition in that fashion. You just need to concatenate the variable to your SQL string:

IF @.filter IS NOT NULL
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder WHERE ' + @.filter
EXECUTE sp_executesql @.sqlString
END
ELSE
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder'
EXECUTE sp_executesql @.sqlString
END

Terri|||And I should add that this approach is extremely vulnerable to SQL injection. The @.Filter value should NOT be coming directly from user input.

Terri|||"And I should add that this approach is extremely vulnerable to SQL injection."

This is what I was afraid of... I posted previously about avoiding SQL injection and I did a little reading, but didn't find anything good. What can I do to avoid SQL injection?

Basically what I want to do is have filterable datagrids and I am using stored procedures for all of my SQL. So the only way I can see to have filterable datagrids (short of writing a stored procedure for every possible combination of filters) is to write a dynamic SQL statement that passes the WHERE clause in.

Is there a better solution? For instance, one thing I was thinking of is, instead of passing in the filter as one argument, I could pass in each field's filter value seperately. Howere, that would mean the stored procedure would take a ton of arguments, not to mention the fact that everytime the table definition changes, the stored procedure would have to change as well, along with the interface.

Jason Pacheco|||You could possibly do something like this:

SELECT
 *
FROM
 TC3_JobOrder
WHERE
 JobNumber = ISNULL(@.JobNumber,JobNumber) AND
 CustomerNumber = ISNULL(@.CustomerNumber,CustomerNumber) AND
 JobDate BETWEEN ISNULL(@.StartDate,'19000101') AND ISNULL(@.EndDate,GETDATE())

To prevent SQL injection you would need to completely validate the user's input, plus make sure that the SQL user account has limited permissions so that if an injection attack is successful the damange is minimized. This isn't the approach I normally take so I don't have firsthand experience.

But I can share with you something that David Penton advised last year on using Dynamic Where clauses, and I don't think he'd mind me sharing it here:
**************************************************************
Date: Tue, 10 Jun 2003 09:13:39 -0600
From: "David L. Penton"
To: aspnet-databases@.aspadvice.com
Subject: Re: [aspnet-databases] RE: Optional Stored Proc Query Parameters All headers

I have a method which I have been using which I like alot. Let's say you have 3 things you want to filter on, but you need to allow any combination of the three.
For simplicity's sake, let's also say that you require at least one value. I am also leaving a few things out for clarity:

CREATE PROCEDURE dbo.myProc
@.val1 int = NULL
, @.val2 varchar(10) = NULL
, @.val3 decimal(12, 0) = NULL
AS

SET NOCOUNT ON

DECLARE @.sql nvarchar(4000)

-- I require a WHERE clause, but you may want to
-- modify it to not need one
IF @.val1 IS NULL OR @.val2 IS NULL OR @.val3 IS NULL BEGIN
RAISERROR('Must have at least one value for @.val1, @.val2, or @.val3', 11,
1)
RETURN (1)
END

-- make base SQL statement
SET @.sql = N'
SELECT
a.val1, a.val2, a.val3, a.val4, a.val5 FROM
dbo.myTable a
WHERE
1 = 1
@.val1REP
@.val2REP
@.val3REP'

-- make logical replacements
IF @.val1 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val1REP', 'AND a.val1 = @.val1') ELSE
SET @.sql = REPLACE(@.sql, '@.val1REP', '')

IF @.val2 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val2REP', 'AND a.val2 = @.val2') ELSE
SET @.sql = REPLACE(@.sql, '@.val2REP', '')

IF @.val3 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val3REP', 'AND a.val3 = @.val3') ELSE
SET @.sql = REPLACE(@.sql, '@.val3REP', '')

--select @.sql
EXECUTE "dbo"."sp_executesql"
@.sql
, N'@.val1 int, @.val2 varchar(10), @.val3 decimal(12, 0)'
, @.val1 = @.val1
, @.val2 = @.val2
, @.val3 = @.val3

RETURN 0

GO

This has a benefit of:

[] preventing SQL injection because you are using parameterized queries

[] caching query plans because you are using sp_executesql

[] controlling the data coming in to the procedure

I actually would do a little more with the '1=1' part, but for email clarity I am not.
Also, in my queries I typically have values that are always required so the '1=1' isn't necessary.

hth...

David L. Penton, Microsoft MVP SQL Server
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach"
david@.davidpenton.com

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
New SP3 SQL BOL: http://www.davidpenton.com/sqlbol

**************************************************************

Also, I think this Patterns & Practices is a good read:Improving Web Application Security: Threats and Countermeasures,Building Secure ASP.NET Applications, in particularChapter 14 - Building Secure Data Access.

Terri|||Alright, I went with a parameterized stored procedure similar to what you have proposed. However, I have one remaining problem. One of the values I am passing in is tied directly to a textbox on the frontent and is very subject to SQL injection. What is the best way of validating this control before its value is passed to the sproc?

I thought about modifying permissions, but it's an ASP.NET application so the sproc runs in the context of the ASP.NET user, who already has EXEC permissions to the sproc, and that can't be modified.|||It took me a while to write that reply and it's not as organized as it could be otherwise.
I mixed my message somewhat. If you use parameters SQL injection should not be an issue.

Since I haven't used dynamic SQL which is built directly from user input I have not developed a strategy to handle this myself. I know you should validate input to make sure the data is of the datatype you are expecting. But if you have a freeform textbox where the user can type in " AND City = 'Dallas'" then that is really a gaping hole.

Hopefully others can chime in on this.

Here's a brief synopsis I found from SQL Server MagazineInjection Protection. And I see thatChapter 10 of the link I gave you previously has a lot of meaty information about validating input.

Terri

problem with datetime value as parameter value

Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320' in the query command part in Microsoft SQL Server Management Studio and it works but when I try to create a report dataset using the stored proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)

String was not recognized as a valid DateTime. (mscorlib)

BUTTONS:
OK

Anyone have any idea on how can I solve it or go about it? Thanks in advance.
Daren
Try using the format: "mm/dd/yyyy", so it would be: 03/20/2006.|||Thanks Deepak,
This solved my problem.
Daren

problem with dates in parameter

Report query uses @.startDate and @.endDate for current date and tomorrow.
For currentdate I use =Today and that seems ok but if the report is run at
3PM the time shows 3PM rather than 00:00:00.
EndDate is more problematic. I've tried many different suggestions from
within this forum for obtaining tomorrow's date, including
=DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
right.
Can someone please help correct the syntax? Thanks!Do you need to use the full date and time, or is it sufficient to use date?
If you only need the date, you could get your dates by doing this sql query:
select convert(varchar(10), getdate(), 104) as today, convert(varchar(10),
dateadd("d", 1, getdate()), 104) as tomorrow
Just switch 104 to get the right date format.
Kaisa M. Lindahl Lervik
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:5D023CCF-CCB6-4A2B-960E-74091184774F@.microsoft.com...
> Report query uses @.startDate and @.endDate for current date and tomorrow.
> For currentdate I use =Today and that seems ok but if the report is run at
> 3PM the time shows 3PM rather than 00:00:00.
> EndDate is more problematic. I've tried many different suggestions from
> within this forum for obtaining tomorrow's date, including
> =DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
> right.
> Can someone please help correct the syntax? Thanks!