How Do You Build A Dynamic WHERE Statement?

Mar 9, 2006

I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext.

How would I go about building this efficiently?

Would I Declare a bit value in the sp called WhereSet = 0

IF @client IS NOT NULL
     IF @WhereSet = 0
          SET @Where = 'WHERE ClientID=@client'
          SET @SetWhere = 1
    ELSE
          SET @Where = @Where + ' AND CleintID=@client'
    .
    .
    ....

Or would this be a lot easier using adhoc SQL instead of a Stored Procedure?

(note: I am using a SQL DataSource)

Please help, I am going bald from pulling my hair our...

View 2 Replies


ADVERTISEMENT

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')

Thanks for your help

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

Hello,

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyName = 'CNN'
AND CompanyName = 'AOL'
AND CompanyName = 'ABC'


P.S I know that above select doesn€™t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.

Thank you

View 8 Replies View Related

Trying To Build Dynamic Stored Procedure

Jun 6, 2008

My existing ASP 1.0 site keeps getting hacked using SQL injections.  I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck.  Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch; 
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ',');     int AStop = SqlKWSrch.Length;     int i = 0;        foreach( string a in SqlKWSearch )       {           if (i <= AStop)           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND ";           }           else           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' ";           }            i++;       }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
 
Thank you
 

View 5 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Build Dynamic Table Columns Issue

Jun 4, 2004

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!

J827

View 4 Replies View Related

SQL Server 2012 :: Build Search Condition Dynamic

Oct 6, 2015

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'

[code]....

i am trying to build a dynamic where "or" clause finding difficulties.

View 7 Replies View Related

Dynamic Build SQL In Store Procedure Based On Select

Jul 23, 2005

I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL

View 3 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Cannot Build This Custom Select Statement...

Jul 20, 2006

I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this:
SELECT     filenameFROM         pictableWHERE     (@searchby LIKE @searchwords)
It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works:
SELECT     filenameFROM         pictableWHERE     (keywords LIKE @searchwords)
Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail.
Thanks in advance.

View 6 Replies View Related

Build SQL Statement To Show Some Tiers

Jun 8, 2005

in my relational sql db structure I have some items and its children, grandchildren itemsto print these values do i need to build one sql statement for each tier?into my sproc i tried something as following but it returns me only the last tierSELECT * FROM AINNER JOINB ON A.field1 = B.field2INNER JOINC ON A.filed3 = C.field4WHERE A.filed1 = @myParam
thanks a lot

View 1 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Dynamic Cursor/ Dynamic SQL Statement

Oct 24, 2004

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James



-- SQL ---------------

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;



--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.

View 2 Replies View Related

Error On Build Solution, If SSIS Project Is Part Of Build

Mar 22, 2006

Hello,

we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..

C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild

Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View 5 Replies View Related

Dynamic SQL Statement

Apr 14, 2008

 I am trying to write a dynamic sql insert statement in c#, but given this is my first run at creating sql statements dynamically I am having issues.  My reason for creating the sql statement dynamically is because I do not want to insert any items that are null.  Will I have to have a separate string for each section of the statement?  For example:sql1 = "Insert Into Table [test]";sql += "(Column1, Column2, Column3," ;sql2 = "Values" ;sql2 += "(field1, field2, field3";  and then a third section if I were to use parameters.  My thinking here is how do you append to a string in numerous locations?  I will populate the sql strings first with those columns and fields that I know will not be null, and then use if statements to add to each sql string if the field in question != null.  in other words, little help.

View 5 Replies View Related

Dynamic Sql Statement

Sep 30, 2003

I need to create a where clause dynamically reading the values from a temp table.

Example:

select * from #tmp_keyword

k_id keyword
1 like "%DBA%"
2 like "%MSSQL%"
3 like

View 1 Replies View Related

Dynamic SQL Statement Help

Apr 25, 2005

Hi, I try to get the dynamic insert statement script.

See the below statement I'm getting syntax error. How can change this right way script?.




select * into pubs.dbo.employee_temp
from pubs.dbo.employee
where emP_id<>emP_id

Declare @cmd varchar(8000)

set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+ 'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini t+''''+','+''''+lname+''''+')' from pubs.dbo.employee
EXECUTE sp_executesql @cmd

View 4 Replies View Related

Dynamic Sql Statement

Nov 27, 2007

I would like to know if it is possible to pass a table name to the from section of a sql select statement?
Something like:
Declare @paramTable as nvarchar(10)
Set @paramTable = TableName
Select firstname, surname from @paramTable

Is this possible?

View 9 Replies View Related

Need Help With Large Dynamic Sql Statement

Mar 16, 2006

Is there a better way to do this? or is basically how you would write a dynamic SQL Stored Procedure? Also, is this code vulnerable to SQL injection? All of the Parameters are being passed in by a SQL DataSource.
set ANSI_NULLS ONset QUOTED_IDENTIFIER ON
go
-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================
CREATE PROCEDURE [dbo].[pe_getAppraisals]
-- Add the parameters for the stored procedure here@PType nvarChar(50),@Client nvarChar(50),@City nvarChar(50),@ApptDate nvarChar(50),@OrderDate nvarChar(50),@Status nvarChar(50),@AType nvarChar(50),@Text nvarChar(50),@OrderBy nvarChar(50),@SortDir nvarChar(4),@PageSize INT,@PageNum INT
AS
DECLARE
@l_Select nvarChar(4000),@l_From nvarChar(4000),@l_SetWhere bit,@l_PType nvarChar(100),@l_Client nvarChar(100),@l_City nvarChar(100),@l_ApptDate nvarChar(100),@l_OrderDate nvarChar(100),@l_Status nvarChar(100),@l_AType nvarChar(100),@l_Text nvarChar(4000),@l_SortDir nvarChar(4),@l_TotalRecords INT
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON;
IF @OrderBy IS NULL     SET @OrderBy = 'OrderDate'
IF @SortDir IS NULL     SET @SortDir = 'DESC'
IF @SortDir = 'DESC'     SET @l_SortDir = 'ASC'ELSE SET @l_SortDir = 'DESC'
--Initialize SetWhere to test if a parameter has Added the keyword WHERE
SET @l_SetWhere = 0
--Create WHERE portion of the SQL SELECT Statement
IF (@PType IS NOT NULL)BEGIN   SET @l_PType = ' WHERE o.PropertyTypeID=' + @PType   SET @l_SetWhere = 1EndELSE SET @PType = ''
IF (@Client IS NOT NULL)BEGIN   IF @l_SetWhere = 0     BEGIN        SET @l_Client = ' WHERE o.ClientID=' + @Client       SET @l_SetWhere = 1       END      ELSE SET @l_Client = ' AND o.ClientID=' + @Client    ENDELSE SET @l_Client = ''
IF (@City IS NOT NULL)BEGIN   IF @l_SetWhere = 0   BEGIN    SET @l_City = ' WHERE o.City=''' + @City + ''''    SET @l_SetWhere = 1   END   ELSE SET @l_City = ' AND o.City=''' + @City + ''''ENDELSE SET @l_City = ''IF (@ApptDate IS NOT NULL)BEGIN   IF @l_SetWhere = 0   BEGIN     SET @l_ApptDate = ' WHERE o.ApptDate= ''' + @ApptDate + ''''     SET @l_SetWhere = 1   END   ELSE SET @l_ApptDate = ' AND o.ApptDate= ''' + @ApptDate + ''''ENDELSE SET @l_ApptDate = ''
IF (@OrderDate IS NOT NULL)BEGINIF @l_SetWhere = 0   BEGIN     SET @l_OrderDate = ' WHERE o.OrderDate=''' + @OrderDate + ''''     SET @l_SetWhere = 1   END   ELSE SET @l_OrderDate = ' AND o.OrderDate=''' + @OrderDate + ''''ENDELSE SET @l_OrderDate = ''
IF (@Status IS NOT NULL)BEGINIF @l_SetWhere = 0   BEGIN     SET @l_Status = ' WHERE o.StatusID=' + @Status     SET @l_SetWhere = 1   END   ELSE SET @l_Status = ' AND o.StatusID=' + @Status  ENDELSE SET @l_Status = ''
IF (@AType IS NOT NULL)BEGIN   IF @l_SetWhere = 0   BEGIN     SET @l_AType = ' WHERE o.ReportID=' + @AType     SET @l_SetWhere = 1   END  ELSE SET @l_AType = ' AND o.ReportID=' + @ATypeENDELSE SET @l_AType = ''
IF (@Text IS NOT NULL)BEGIN   IF @l_SetWhere = 0   BEGIN   SET @l_Text = ' WHERE (o.FileNumber LIKE ''' + @Text + '%''' +   ' OR o.LoanOfficer LIKE ''' + @Text + '%''' +   ' OR o.Borrower LIKE ''' + @Text + '%''' +   ' OR o.StreetAddrA LIKE ''' + @Text + '%''' +   ' OR o.State LIKE ''' + @Text + '%''' +   ' OR o.ContactName LIKE ''' + @Text + '%'')'   SET @l_SetWhere = 1ENDELSE SET @l_Text = ' AND (o.FileNumber LIKE ''' + @Text + '%''' +   ' OR o.LoanOfficer LIKE ''' + @Text + '%''' +   ' OR o.Borrower LIKE ''' + @Text + '%''' +   ' OR o.StreetAddrA LIKE ''' + @Text + '%''' +   ' OR o.State LIKE ''' + @Text + '%''' +   ' OR o.ContactName LIKE ''' + @Text + '%'')'ENDELSE SET @l_Text = ''
--Build the SQL SELECT Statement
SET @l_Select = 'o.OrderID AS OrderID, o.FileNumber AS FileNumber, o.OrderDate AS OrderDate, o.ClientID AS ClientID, o.ClientFileNumber AS ClientFileNumber, o.PropertyTypeID AS PropertyTypeID, o.EstimatedValue AS EstimatedValue, o.PurchaseValue AS PurchaseValue, o.LoanOfficer AS LoanOfficer, o.ReportFee AS ReportFee, o.FeeBillInd AS FeeBillInd, o.FeeCollectInd AS FeeCollectInd, o.CollectAmt AS CollectAmt, o.Borrower AS Borrower, o.StreetAddrA AS StreetAddrA, o.StreetAddrB AS StreetAddrB, o.City AS City, o.State AS State, o.Zip AS Zip, o.ContactName AS ContactName, o.PhoneA AS PhoneA, o.PhoneB AS PhoneB, o.ApptDate AS ApptDate, o.ApptTime AS ApptTime, o.AppraiserID AS AppraiserID, o.InspectionDate AS InspectionDate, o.DateMailed AS DateMailed, o.TrackingInfo AS TrackingInfo, o.ReviewedBy AS ReviewedBy, o.StatusID AS StatusID, o.Comments AS Comments, o.SpecialNotes AS SpecialNotes, o.EmailInd AS EmailInd, o.MgmtName AS MgmtName, o.MgmtContactName AS MgmtContactName, o.MgmtAddress AS MgmtAddress, o.MgmtPhone AS MgmtPhone, o.MgmtFax AS MgmtFax, o.MgmtFee AS MgmtFee, o.MgmtNotes AS MgmtNotes, o.LoginName AS LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc AS StatusDesc, ot.ReportDesc AS ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq AS ReportSeq, pc.PriceDesc AS PriceDesc, pt.PropertyTypeDesc AS PropertyTypeDesc, l.LoginName AS AppraiserName, l2.LoginName As ClientName'
SET @l_From = 'Orders AS o LEFT OUTER JOINOrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOINOrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOINOrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOINOrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOINPriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOINPropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOINLogins AS l ON o.AppraiserID = l.LoginID LEFT OUTER JOINLogins AS l2 ON o.ClientID = l.LoginID'
SET @l_TotalRecords = @PageSize * @PageNum
PRINT ' ORDER BY ' + @OrderBy + ' ' + @l_SortDir + ') ORDER BY ' + @OrderBy + ' ' + @SortDir
Execute('SELECT TOP(' + @PageSize + ') * FROM (SELECT TOP(' + @l_TotalRecords + ') ' + @l_Select + ' FROM ' + @l_From + @l_PType + @l_Client + @l_City + @l_ApptDate + @l_OrderDate + @l_Status + @l_AType + @l_Text + ' ORDER BY ' + @OrderBy + ' ' + @l_SortDir + ') AS rsltTbl ORDER BY ' + @OrderBy + ' ' + @SortDir)
END
Thank You,
Jason

View 5 Replies View Related

Cannot Run Dynamic Sql Using &#39;USE DB_Name&#39; Statement

Feb 4, 2002

For whatever reason, the following when executed does not take the context of the supplied Database name. Any ideas as to why? More importantly is there a work around. I am trying to create a database level batch job.

declare @sql nvarchar(100)
set @sql = 'USE Northwind'
print @sql
EXEC sp_executesql @sql


Thanks,

Mark

View 1 Replies View Related

Dynamic Statement Needed?

Apr 23, 2014

I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:

select *
from Table
where D10 > midDaypct * 2 and DOW6 > 0
union
select *
from Table
where DOW6 >midDOWpct * 2 and D10 > 0

We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.

View 5 Replies View Related

Using A Dynamic Top Statement With A Cursor

Jul 20, 2005

Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper

View 4 Replies View Related

View SQL Statement For Dynamic SQL

Oct 9, 2007



I am using dynamic sql in my report. I cannot see the sql the report is generating in sql profiler. Is there somewhere else I can view it?
Thanks,
Linda

View 3 Replies View Related

Dynamic Select/Update Statement Possible?

Dec 3, 2004

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable

Just curious.

Thanks,
Steve Hanzelman

View 6 Replies View Related

Best Way To Create Dynamic Update Statement

Jul 23, 2005

In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.

View 6 Replies View Related

Dynamic Statement In Variable - Parseerror

May 8, 2006

I am trying to use this statement in a variable, including another variable:

"SELECT * FROM my_table WHERE CAST([timestamp] AS INT) > " + @[User::LastTimestamp]

But the variable value insists on giving me this error:

The expression for variable "VariableName" failed evaluation. There was an error in the expression.

I cast the columntype "timestamp" to int, and the variable "LastTimestamp is stored as int32, and has a default value of 0. I simply can't grasp what it is I am missing.

Is it because the expression is part string and part integer? If so, how is that avoided?

Thanks in advance

View 2 Replies View Related

SQL Statement For Querying Data With Dynamic Fields

Sep 19, 2006

I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc.  The complicated part is that they want an admin to be able to modify/add/delete the set of properties.  So for example, they could add the attribute "size."   Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item.  I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid.  However, what would the query be to do this?  I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")

View 1 Replies View Related

Spooky Semi-dynamic Sql Update Statement

Nov 9, 2006

Hi,
I rewrote my working Sql statement to prevent Sql Injections. I copied some code I used in another project but this time I can't get it to work, possibly because it's an update statement and not an Insert one, which I used before.
Sorry for the boring question, but does anyone have a clue what's wrong with the syntax?
Here's the original code (I changed the parameter names for clarity and security):
    Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)    Dim strSQL As String = "Update MyTable Set " & typ & num & " = '" & pname & "' WHERE personID = " & fid    Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
Here's the code from codebehind:
    Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)    Dim strSQL As String = "Update MyTable Set (" & typ & num & ") Values (@" & typ & num & ") WHERE personID = " & fid    Dim cmd As New SqlCommand(strSQL, conn)    With cmd.Parameters       .Add(New SqlParameter("@" & typ & num, pname))    End With    TestLabel.Text = strSQL & "        " & pname    cmd.Connection.Open()    cmd.ExecuteNonQuery()    cmd.Connection.Close()   
Here's my test message; first the sql, then the new string to be inserted:
Update MyTable Set (picturename) Values (@pname) WHERE personID = 2       2_adin.jpg  
Here's my error code:
Line 1: Incorrect syntax near '('. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
Source Error:
Line 489:            TestLabel.Text = strSQL & "  " & pnameLine 490:            cmd.Connection.Open()Line 491:            cmd.ExecuteNonQuery()Line 492:            cmd.Connection.Close()Line 493:        End If 
I could understand it if I'd get an error in VWD Express for using dynamic variables, but they work correctly in the text message so I'm clueless. Any help is deeply appreciated!
Pettrer (VB, Sql Server 2000)
 
 

View 2 Replies View Related

Building A Dynamic Sql Statement Into Stored Procedure

Apr 19, 2008

Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]

@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID  = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId  = @regionID '
exec sp_executesql @StaticStr
)

View 10 Replies View Related

Dynamic Column Names In Select Statement

Oct 13, 2001

I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.

declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.

The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.

View 1 Replies View Related







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