SP With Variable Number Filled Of Parameters

Jun 14, 2007

I have a SP search_post (e.g)

@id int,
@author varchar(40),
@keyword varchar(40),


select * from
posts
where
id = @id and author =@author and message like @keyword


in my case a user can choose to search by one or more of this elements.

what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled


one approach I have thought out is this

if (@id IS NOT null)
select * from
posts where id = @id


if (@author IS NOT null)
select * from
posts where author= @author



if (@keyword IS NOT null)
select * from
posts where keyword = (@keyword



but this does not really take care of the posibility that all of them or some of them will be null while others will not

any suggestions ?

View 5 Replies


ADVERTISEMENT

Stored Procs With Variable Number Of Parameters

Oct 26, 2006

I have received a change request for a project i am working on which im unsure if possible or not.

at the minute i have a very simple sp that simply selects data from a table by passing in one parameter

CREATE PROCEDURE PHAR_SelectGrade

@int_ID int

AS

 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID = @int_ID
ORDER BY Grade

the user now wants the option to select more than one grade type (there are 100's of different grades)

so they might decide they want to see details for 2 grades, 22 grades etc. these would be selected and stored in an array (via an asp.net project) and then the stored procedure is called from within a web service

my question is how would i pass this array into the stored procedure??

i am assuming i would need to do something as follows for sp syntax, but im stumped on how i pass my array of values into the sp from the webservice and then for the sp to read the array in SQL??

CREATE PROCEDURE PHAR_SelectGrade

@myArray <what datatype?>

AS

 SELECT GradeID as ID, Grade as Description, RequiresText, SendEmail, Timestamp
 FROM tbl8Grade
 WHERE Obsolete=0
AND GradeID IN (@myArray)
ORDER BY Grade


any ideas are greatly appreciated. or maybe its just not possible?!?

Cheers,
Craig

View 3 Replies View Related

Stored Procedure - Variable Number Of Parameters For Search

Dec 4, 2003

Hi,
I have a repeater control which I populate with search results from SQL Server.

But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?

I'm using a stored procedure with parameters:

input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords

Am using a temporary table to store all records before Select-ing those required for the particular page.

If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.

Am hoping that stored procedures can cope with an unknown number of parameters.

View 3 Replies View Related

Passing In Variable Number Of Parameters To A Stored Procedure

Jul 9, 2006

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
.....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? Thanks

View 7 Replies View Related

Schedule A Report To Run At A Particular Time With User Filled Parameters

Mar 23, 2007

I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.

Thanks,

Chris

View 6 Replies View Related

Forwarding Variable Number Of Parameters From VB.2005 To Sql Server 2005 Stored Procedure

Jan 15, 2008

I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....

View 1 Replies View Related

Help Designing Query For Any Number Of 5 Parameters

Nov 27, 2007

Hello everyone,

I'm having trouble designing a query for an ASP.NET project I'm working on. The main portion of the query is done and works, but I need to modify it to accept 5 parameters. Below is the main query:





Code Block

@LocID as nvarchar(10)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID
The changes I need to make to this are confusing me because I've had to do something like this before. There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information. The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns.

The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment. I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this. A co-worker suggested doing something like this:





Code Block

@LocID as nvarchar(10),
@Gate as nvarchar(50),
@Location as nvarchar(50),
@OffLZ as nvarchar(50),
@OnLZ as nvarchar(50)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate)

AND (dbo.ProfileNames.Location = @LocID)


AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location)
AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ)
AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ)
At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project). If anyone can help, I'd really appreciate it, thanks.

View 3 Replies View Related

Variable Parameters - Which Approach.

Jul 20, 2005

ASP.NET / VB.NETSQL Server 7.0Our client has insisted that we change our established practice ofbuilding SQL in-line and move it all to SPROCs. Not a problem for 80%of the app. However, we have a number of Search/Filter forms thatcontain a number of controls to allow more or less focused searchcriteria to be built up.I have a class that does this quite nicely, examining all controls forvalid input and, if found, concatenated onto the end of what becomes a"WHERE" clause. Once all is done the "WHERE" clause is appended to aSQL Statement and passed to an ExecuteReader function. Only atruntime is it known how many, and which, controls will be used in thefilter.I can think of two methods of approaching this variable parameter listwhich will be required if moving to SPROCs.METHOD 1.Create a SPROC where ALL the possible parameters are used. Forexample:CREATE PROC stpSearchContactsUsingContactRef@ContactRef varchar(50),@Status varchar(50),@IgnoreDate int,@Date1 smalldatetime,@Date2 smalldatetimeASSELECTtblContactSources.*FROMtblContactsWHERE(tblContacts.fldContactID LIKE @ContactRef) AND(tblStatuses.fldStatus LIKE @Status) AND((tblContacts.fldDateCreated >= @Date1) AND(tblContacts.fldDateCreated <= @Date2) OR @IgnoreDate = 0)GOIf either of the parameters @ContactRef and @Status is empty, the codethat retrieves the filter values passes in the value '%', otherwise itwill pass in the user's choice. If the user wants not to search on thedate range, then @Date1 and @Date2 are passed in with '' and@IgnoreDate is set to 0.METHOD 2.Create a SPROC which consists of SQL body as a string, with the"WHERE" clause passed in as a string parameter, and both EXEC'd -CREATE PROCEDURE stpTSRGetFilteredTSR@WhereClause as varchar(3999)ASDECLARE @Body as varchar(3999)DECLARE @OrderBy as varchar(3999)SET @Body = 'SET CONCAT_NULL_YIELDS_NULL OFFSELECT TOP 100 PERCENTtblTSRs.fldTSRID, tblTSRs.fldFaultDescription,tblTSRs.fldDateEntered, tblTSRPriorities.fldTSRPriority,tblTSRs.fldTSRPriorityID, tblVehicles.fldRegistrationNumber,tblVehicles.fldVIN, tblTSRStatuses.fldTSRStatus,tblTSRs.fldTSRStatusID, tblTSRs.fldDealerID, tblTSRs.fldEmergency,tblCustomers.fldFirstName + '' '' + tblCustomers.fldSurname ASfldCustomerNameFROMtblContactReasonsINNER JOIN tblContacts ON tblContactReasons.fldContactID =tblContacts.fldContactIDINNER JOIN tblCustomers ON tblContactReasons.fldRegardingID =tblCustomers.fldCustomerIDINNER JOIN tblVehicles ON tblContactReasons.fldVehicleID =tblVehicles.fldVehicleIDRIGHT OUTER JOIN tblTSRs WITH (NOLOCK)LEFT OUTER JOIN tblTSRStatuses ON tblTSRs.fldTSRStatusID =tblTSRStatuses.fldTSRStatusIDLEFT OUTER JOIN tblTSRPriorities ON tblTSRs.fldTSRPriorityID =tblTSRPriorities.fldTSRPriorityID ON tblContacts.fldContactID =tblTSRs.fldContactIDWHERE (GETDATE() <= DATEADD(week, 2, tblTSRs.fldDateEntered))'SET @OrderBy = 'ORDER BY tblTSRs.fldDateEntered'SET @Body = @Body + @WhereClause + @OrderByEXEC (@Body)GOIn this example, the @WhereClause parameter might typically be:' AND (fldDealerID = 9) AND (fldTSRStatusID = 1) 'My question is, is there anything to recommend one method over theother?TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk

View 3 Replies View Related

Passing Different Number Of Parameters To A Stored Procedure

Jan 22, 2007

Hi to all,

How can I Pass different number of parameters to a Stored Procedure?

In my Requirement,

Some times i want to pass 2 parameters only,

In some cases i want to pass 6 parameters.

How can i do this?

Please give me a solution.

Thanx in advance...

View 1 Replies View Related

Maxmimum Number Of Parameters For OleDB Command

Oct 31, 2007

I have a stored proc with 28 commmands but only 24 are loaded. If I move the parameters around the ones that didn't show are visible and the ones after the 24th parameter don't show up. Is there some sort of limit on parameters for OLEDB commands that execute stored procs in the exec procname @p = ? construct?

View 3 Replies View Related

Efficient Queries With Variable #'s Of Parameters

Dec 13, 2006

I want to create a web page where a user can select from 1-100 fields to include in output, and then query a table to only return the fields the user has selected.

I do not want to construct a dynamic SQL SELECT statement. I would rather use a stored procedure, but am not certain how to only return the fields that the user selected.



View 1 Replies View Related

Transactions With Variable Number Of Inserts

Nov 30, 2004

I'm stumped how to achieve my goal using stored procedures. I have a front end application that calls stored procedures to insert data. For example, customer pays a bill. To complete the payment process a command in the app is run which calls a stored procedure to insert a tender record and a second stored procedure to insert a transaction record. A customer can pay one to many bills at one time so the number of times the insert stored procedures are called is variable. I want to be able to use the begin transaction, commit and rollback statements so if there's a problem inserting a record all the inserts are rolled back. Previously the app used the visual basic begin transaction, commit, rollback, statements however I want to now use SQL stored procedures for inserts and updates. I've been struggling with this for some time and hope someone can help me.

View 4 Replies View Related

Using Variable Number Of Search Strings With VBA

Mar 5, 2015

I have created a kind of search function in my Excel sheet where the user can select multiple items from different listboxes.

To save the items I use an array. So in one case only 1 items off the array is filled, but another time maybe 3 items are filled in the array.

I use the array to search in a SQL database (this is string rva(i) in the code). But when the array is empty it results in an error.

This is what I have so far. (the rule at the bottom won't work also )

rc.Open "SELECT [Datum],[RvA_Nr], [RvA_Letter], [Afdeling], [EVAL], [Matrix], [Component], [AS3000], [AP04], [Rec] FROM dbo.QHSE_2ndline_history " _
& "WHERE [Afdeling] = '" & afd & "' AND [Datum] >= '" & datum1 & "' AND [Datum] <= '" & datum2 & "' AND " _
& "CASE WHEN '" & rva(1) & "' <> '' then [RvA_Nr] = '" & rva(1) & "' END AND" _
& "([Matrix] LIKE '%" & matrix(1) & "%' OR [Matrix] LIKE '%" & matrix(2) & "%' OR [Matrix] LIKE '%" & matrix(3) & "%' OR

[Code] .....

View 1 Replies View Related

How To Handle A Variable Number Of Self-joins?

Nov 9, 2007

Background: Customer sale records are kept in table (userHistory) that records: bookID, userID, and salesDate.

Objective: To find all the customers that have purchased the same books as any given customer, say customer1. For example, customer1 bought books bookA, bookB, and bookC; the goal is to find all of the other customers who also bought these books.

My First Attempt:

SELECT DISTINCT UH1.userID
FROM userHistory as UH1, userHistory as UH2, userHistory as UH3
WHERE UH1.userid = UH2.userID
ANDUH2.userid = UH3.userID
ANDUH1.bookID = ‘bookA’
ANDUH2.bookID = ‘bookB’
ANDUH3.bookID = ‘bookC’
AND UH1.userID <> ‘customer1’

The problem here is that it cannot handle any more/less than 3 books. The above should work well for 3 books, but what if there are 2 books or 12 books? Does anybody have any suggested queries to handle a variable amount of books (eg joins)?

Thank you kindly.
Adam

View 6 Replies View Related

Variable Number Of Input Columns

May 2, 2007

We are trying to use the Import/export wizard to load a text file to a SQL Server 2005 database. The input file has a variable number of columns per row. For example, the first row has 3 columns, the second has 7, the third has 3, etc. The number of columns varies from 2 to 9 in the input file. The columns are separated by an uptick (`) and the rows are terminated by {CR}{LF}. We are using code page 1252. On processing, the wizard reads the first row (with 3 columns) ok, but then assumes all the other rows have 3 columns and parses the rows accordingly, ignoring the field and row terminators.

The process worked fine with SQL Server 2000. Is there some setting that we are missing, or some configuration on the database that we should be checking?



Thank You

View 3 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008



Hi, I need to update a number of columns in a number of tables - I just don't know how many. In this case, I am updating all varchar fields and nvarchar fields to be converted to lower case. The problem is that the table structure is amended over time as columns are added programmatically, so I do not know which tables have which columns and if so which of them are varchars.

I can get a table of which fields I need to update using:

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'

and I can do the update with UPDATE tblxxx SET column = LOWER(column)

But what I don't know is how to step through my temporary table and do the updates. I can do it in ASP.NET, but that involves pushing commands and data between ASP and SQL, and will be too slow. How do I do it in SQL?

Thanks,

Dave Stephens

View 3 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008

Hi,



I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.



I know that I can get a lit of columns using:



SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'


and do the update using:



UPDATE tblABCDE SET column = LOWER(column).



In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.



If so, how do you do it?



Thanks,



Dave Stephens

View 1 Replies View Related

How To UPDATE A Variable Number Of Columns

Feb 6, 2008

Hi,



I need to update a number of columns within a number of tables - I just don't know how many. In this case, I want to convert all varchar and nvarchar columns to lower-case versions of themselves. The problem is that the table structure is changed programatically, and so at any point in time I cannot be certain what fields are in which table, and what data type they are.



I know that I can get a lit of columns using:



SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
INTO tblTempLCase
FROM information_schema.columns
WHERE DATA_TYPE LIKE '%varchar'


and do the update using:



UPDATE tblABCDE SET column = LOWER(column).



In ASP.NET I can pull in this temporary table using a SQL Data Adapter, and then step through the records to formulate the UPDATE statements and execute them all. However, I hope that this is possible in SQL too, so that I do not have to keep firing data/commands between ASP and SQL, as it should be quicker, and is also neater.



If so, how do you do it?



Thanks,



Dave Stephens

View 1 Replies View Related

SqlDataSource With Different Number Of Columns And Insert Parameters And FormView

Dec 7, 2005

Dear All,
i have a SqlDataSource with a simple select command(e.g. "select a,b,c from foo"). The insert command is a stored procedure and takes less parameters than there are columns in select statement (e.g. "insertFoo(a char(10))").
When used in combination with form view, i get "Procedure or function insertFoo has too many arguments specified" error.
It seems that form view always posts all columns as parameter collection (breakpoint in formview_inserting event shows this) to insert command.
Am I doing something wrong or is this by design? Is the only solution to manualy tweak parameters in formview_inserting event?
TIA
   Jernej

View 2 Replies View Related

Stored Procedure:high Number Of Output Parameters

Feb 23, 2008

If I want to get back about 30 strings as output parameters from a stored procedure, what is my best bet? Each string is upto 50 characters each.

Do I send them back individually as seperate parameters? Return as a large parsed string? Return as XML?

Thanks!

View 1 Replies View Related

Transact SQL :: Can A Procedure Be Created That Takes Any Number Of Parameters

Aug 28, 2015

I am having to debug a procedure that is called by a control in javascript that I do not have any control over accept for setting the procedure name to call.

I matched my parameters to my procedure to what I define as the parameters list, but somehow I keep getting a too many parameters specified.

If I had control over it in C# it would be easy to select which params are actually being sent, but I would like to give a procedure name to call and I can log the parameters sent somewhere.how to do.

View 5 Replies View Related

Updating/getting Values From Datagrid In C# With Variable Parameters

Oct 24, 2006

HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT

View 1 Replies View Related

Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page.  It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results.  I've tried several variations of:         string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'";        e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much! 

View 3 Replies View Related

Setting Variable Parameters In A SSRS Subscription

Nov 7, 2007

I would like to set up a subscription that has two date parameters, I would like the end_date to be today and the start_date to be (today - 1 Month). The interface does not seem to support expressions?

I saw some documentation that said to use defaults in the report but that does not help because I may want multiple subscriptions with different params like 1 person may want (today - 2 months) as the start date ...

Any help would be appreciated

View 9 Replies View Related

SYSBACKUPHISRY IS GETTING FILLED

Apr 26, 1999

Hi Guys,
we have a database where we have backup and restore done daily basis by a automated task. We are having errors on MSDB the saying database is reached the capacity(as SYSBACKUPHISRY getting populated every day). After I increase the size of MSDB,the error msg disappeared.

Does anyone know rather than increasing the size, Can we somehow store the data upto somany records and then when it reach the max, to rewrite the SYSBACKUPHISRY table all over again?

Is it advisable to truncate the sysbackup history table?

I would really like some one to give an idea as soon as posible. I appreciate it really very much.
Thanks,
Jay.

View 1 Replies View Related

Can The Transaction Log Be Filled.?

May 25, 2006

can the transaction log be filled.?
lets say i deleted millions of rows. does it get filled?

View 1 Replies View Related

How To Get Last Date Only Once All Have Been Filled In

Aug 8, 2006

I want to be able to return the last date, e.g.(select Max(DateColumn) From Table)However if any of the DateColumn fields are null I want to return anull, is there a simple way to do this?

View 2 Replies View Related

Auto Insert A Variable Number Of Records?

Sep 20, 2006

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC  combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam

View 3 Replies View Related

How Can I Have A Variable Number Of Parameter Values In A Dataset?

Apr 20, 2007

I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter.  The problem is I don't know how many.  I have a textbox that the user can enter search words in.  The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray)    strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method?  How?  If I can't, what are my options?Diane 

View 6 Replies View Related

Variable Number Of Where Clauses In A Stored Procedure

Jun 20, 2001

I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?

A simple illustation of this is as follows
A statement with two clauses :-

Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'

The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back

View 2 Replies View Related

Variable Number Of Columns In A Temporary Table

Nov 8, 1999

How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.

View 1 Replies View Related

MS SQL Problem: Fill Down Variable Number Of Rows From Last Known Value.

May 15, 2006

Using Microsoft query analyser SQL version 8, I believe.

Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!

________________

Update dbo.NSTS

Set dbo.NSTS.Number = NSTS_1.Number

FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)

WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')

________________

Example of data table here:

ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
_________________

I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).

Help would be very much appreciated!

View 6 Replies View Related

PIVOT Operator For Variable Number Of Transformations

Feb 19, 2007

Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:

SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt


this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved