Passing IN() values as parameter

Hi...

I'm creating a procedure to fetch rows from table. One field will come come as IN(). Its the condition. That field is numeric field (note down), i would like to pass the In values as parameter.

eg: procedurename @.fieldvalue varchar(100)
as
begin
...

WHERE fieldname IN(@.fieldvalue)

while executing the procedure how to pass the value... or procedure itself has problem...?
Help me...

Tx in Advance...Once again dynamic SQL seems to be the deal here, unless a more complete description of the problem is available.
The thing here is that you cannot do a procedure that works in the way you describe without using dynamic SQL, because of the datatype clash.

Your IN clause wants to do comparisons with integers, and therefore your argument to the procedure will never be able to work unless you convert your values in the fieldvalue variable to a string which you add to the the last part of your existing query, and execute it with dynamic SQL. I cannot judge whether this is the right decision to do in your particular case without more information about the problem. Hope I got your thinking going atleast ...|||In your procedure, parse the parameter into a temp table. Join your temp table to the production table to limit the result set. Works every time, and often runs much faster than the dynamic SQL solution.

-PatP

Passing Date Variables

I am a newbie and need help. I am creating an intranet site for my company
and my SQL2000 database has a column named transdate. I want to provide the
user with the ability to run a canned report depending on a date range
entered in a web form (Start Date / End Date).
How would I pass these form entries to my SQL select statement? I am using
ASP VBScript
I'm thinking...
Select *
from DB where transdate >= ' and transdate <= '
Your help is greatly appreciated!!!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
You can use a parameterized SQL statement, specifying '?' as parameter
markers:
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = "2006-05-01"
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = "2006-05-31"
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:615867ef9c88a@.uwe...
>I am a newbie and need help. I am creating an intranet site for my company
> and my SQL2000 database has a column named transdate. I want to provide
> the
> user with the ability to run a canned report depending on a date range
> entered in a web form (Start Date / End Date).
> How would I pass these form entries to my SQL select statement? I am using
> ASP VBScript
> I'm thinking...
> Select *
> from DB where transdate >= ' and transdate <= '
> Your help is greatly appreciated!!!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Thanks Dan, I'll give it a shot
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||Obviously I am not advanced enough to get this? I need to pass the dates
from my Web form to the embedded SQL statement in Dreamweaver. I am using
multiple recordsets that require these same date ranges. In ACCESS it is eas
y
because you can create the form and reference it. Is there anything like thi
s
in SQL?
Dan Guzman wrote:
>You can use a parameterized SQL statement, specifying '?' as parameter
>markers:
>Set command = CreateObject("ADODB.Command")
>command.ActiveConnection = myConnection
>command.CommandText = "SELECT * FROM MyTable WHERE transdate >= ? AND
>transdate <= ?"
>Set fromDateParameter = command.CreateParameter( _
> "@.fromDate", adDate, adParamInput)
>command.Parameters.Append fromDateParameter
>fromDateParameter.Value = "2006-05-01"
>Set toDateParameter = command.CreateParameter( _
> "@.toDateParameter", adDate, adParamInput)
>command.Parameters.Append toDateParameter
>toDateParameter.Value = "2006-05-31"
>
>[quoted text clipped - 11 lines]
Message posted via http://www.webservertalk.com|||> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
SQL Server is basically just the back-end database engine. Unlike SQL
Server, Access also includes an IDE so that you can develop a 'rich client'
GUI for your users. The Access database engine isn't a client/server DBMS
because Access runs in the client process and, in the case of a multi-user
application, the Access database file is shared among multiple Access
instances. With SQL Server, it is the database engine is shared and only
that SQL Server instance accesses the database files.
I know next to nothing about Dreamweaver so I can't provide detailed help.
I don't know what an 'embedded SQL statement in Dreamweaver' is. I assume
this part of server-side code (ASP or ASP.NET) that is generated by the
Dreamweaver IDE. I would expect that the IDE would provide some method to
parameterize the SQL statement, map to your form variables and associate
with a SQL Server database connection.
don't know if this will help but below is an ASP VBScript snippet that can
execute a SQL statement based on the date range. I would expect Dreamweaver
would generate something similar.
<!-- include ADO constants -->
<!-- METADATA
TYPE="typelib"
UUID="00000200-0000-0010-8000-00AA006D2EA4"
-->
<%
Set connection = CreateObject("ADODB.Connection")
connection,Open "Provider=SQLOLEDB;Data Source=MyDbServer;Integrated
Security=SSPI"
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = myConnection
command.CommandText = "SELECT * FROM MyTable " & _
"WHERE transdate >= ? AND transdate <= ?"
Set fromDateParameter = command.CreateParameter( _
"@.fromDate", adDate, adParamInput)
command.Parameters.Append fromDateParameter
fromDateParameter.Value = Request("fromDate")
Set toDateParameter = command.CreateParameter( _
"@.toDateParameter", adDate, adParamInput)
command.Parameters.Append toDateParameter
toDateParameter.Value = Request("toDate")
Set results = command.Execute
While results.EOF = False
'process row here
results.MoveNext
Loop
results.Close
connection,Close
&>
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:61bd9dcc71568@.uwe...
> Obviously I am not advanced enough to get this? I need to pass the dates
> from my Web form to the embedded SQL statement in Dreamweaver. I am using
> multiple recordsets that require these same date ranges. In ACCESS it is
> easy
> because you can create the form and reference it. Is there anything like
> this
> in SQL?
> Dan Guzman wrote:
> --
> Message posted via http://www.webservertalk.comsql

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" < __ron__dontspamme@.wedontlikespam_garagei
q.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variab
le
> number of parameters? I'm creating a stored proc to populate a Suggested P
O
> form. It may require filtering on one or more vendor IDs (string data). I'
ve
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it loo
ks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass variable number of parameters to a stored proc

Is it possible to create a stored proc that allows the passing of a variable
number of parameters? I'm creating a stored proc to populate a Suggested PO
form. It may require filtering on one or more vendor IDs (string data). I've
looked through BOL but nothing is jumping out at me. I also was looking at
the string manipulation functions for parsing a single variable but it looks
like it would be ugly. TIA!Hi,
You will have to use dynamic SQL inside the procedure to parse the parameter
variable which hold one or more input values.
See the below URL for the various usage of dynamic SQL.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
SQL Server MVP
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23RizK7jxGHA.480@.TK2MSFTNGP06.phx.gbl...
> Is it possible to create a stored proc that allows the passing of a
> variable
> number of parameters? I'm creating a stored proc to populate a Suggested
> PO
> form. It may require filtering on one or more vendor IDs (string data).
> I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it
> looks
> like it would be ugly. TIA!
>
>|||Ron Hinds wrote:
> Is it possible to create a stored proc that allows the passing of a variable
> number of parameters? I'm creating a stored proc to populate a Suggested PO
> form. It may require filtering on one or more vendor IDs (string data). I've
> looked through BOL but nothing is jumping out at me. I also was looking at
> the string manipulation functions for parsing a single variable but it looks
> like it would be ugly. TIA!
>
>
Here is one approach:
CREATE PROCEDURE MyProc
@.Var1 INT = NULL,
@.Var2 INT = NULL
AS
SELECT Field1, Field2
FROM Table
WHERE
((@.Var1 IS NULL) OR (Field3 = @.Var1))
AND
((@.Var2 IS NULL) OR (Field4 = @.Var2))
The optimizer is smart enough to realize that if the left side of the OR
is true, there is no need to evaluate the right side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Pass concatenated string to SPROC

Hello,

We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.

My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?

Will I have to create Dynamic SQL to do this?...or... can I do something like this...

@.ConcatenatedString --eg. 1,2,3,4,5,6,7
SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @.ConcatenatedString

Thanks for your help.you need to use IN.

hth|||You can to dynamically build an SQL string using the IN keyword and then execute it. Or, you can build a function to parse the list and return a table of category ids. Then use you IN clause with a select statement from the table or you could join to the table that's returned. I'd just do the dynamic SQL, but you could try both to see what's faster in your situation.

Partitionning a Table

Hi guys,

I am creating a program, and for that i implements function to partionate a Table (which are too big).
In Oracle it's simple :
ALTER TABLE myTable SPLIT PARTITION ....

But for MS Sql Server 2005, I encounter some problems.

Do you have an idea ?
How can i partitionate a Table without make TSQL Functions ?
Is it possible ?
how can I split Tables simply.

Thanks ;)

I don't think you can...

this article describes how to do it using a function:

http://msdn2.microsoft.com/en-us/library/ms186307.aspx

|||thanks ;)