Best Way To Create Dynamic Update Statement
Jul 23, 2005In general, What is the best approach in creating a dynamic update
stored procedure, that can handle recieving varying input paramters and
update the approporiate columns.
ADVERTISEMENT
SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement
Jan 9, 2015Ok 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.
Dynamic CREATE TABLE Or SELECT INTO Statement
Jul 27, 2004In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
BTW - this would be a stored procedure...
Any suggestions would be greatly appreciated.
Dynamic Select/Update Statement Possible?
Dec 3, 2004Would 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
Spooky Semi-dynamic Sql Update Statement
Nov 9, 2006Hi,
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)
T-SQL (SS2K8) :: Generate Dynamic Update Statement
Aug 14, 2014I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
Create Batch File That Will Run Update Statement And Schedule It To Run?
Oct 7, 2013Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
View 7 Replies View RelatedDynamic Create Table, Create Index Based Upon A Given Database
Jul 20, 2005Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedDynamic Cursor/ Dynamic SQL Statement
Oct 24, 2004I'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 ':'.
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
Aug 29, 2006I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedSQL Server 2012 :: Update Statement With CASE Statement?
Aug 13, 2014i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
Transact SQL :: Update Statement In Select Case Statement
May 5, 2015I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Jul 23, 2005Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies View RelatedJDBC 2005 Update Statement - Failing Multi Row Update.
Nov 9, 2007It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies View RelatedHow To Create A Update Button To Update Two SqlDataSource Controls?
May 10, 2006I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Jul 30, 2007Hello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
Dynamic SQL Statement
Apr 14, 2008I 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 RelatedDynamic Sql Statement
Sep 30, 2003I 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
Dynamic SQL Statement Help
Apr 25, 2005Hi, 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
Dynamic Sql Statement
Nov 27, 2007I 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?
Dynamic SQL- Create Table
Sep 26, 2001Is it possible to create a temp table using exec. e.g
exec('create table #temp1 (xyz int, abc int)')
when I run the above statement, I dont get an error but nothing happens.
The reason I need to create it dynamically is that in my stored proc, I am passed a number. This number determines how many columns my temp table should have.
thanks
Zoey
Dynamic SQL Create Table
Oct 13, 2005The following dynamic SQL script works for creating a table on the fly but if I change the select @tmpTblNm = 'tmpABC' to select @tmpTblNm = '#tmpABC'
it will not create the temp table. Can anyone help on creating a temp table dynamiclly?
declare @tmpTblNm varchar(40),
@str varchar(1000)
select @tmpTblNm = 'tmpABC'
select @str = ''
-- Create a temp table to hold the current page of data
-- Add an ID column to track the current row
select @str = 'Create Table '+ @tmpTblNm +
' ( row_id int IDENTITY PRIMARY KEY,
customerID int,
customerName varchar(40),
address1 varchar(40),
city varchar(25) )'
exec (@str)
Dynamic Create Table
Apr 2, 2007Hi guru !!
I want to create a table that can created dynamicly based on front end application.table should handle insertion and edit also. please help me to get out of this situation.
thanks
shekhar
How To Create Dynamic Controls In Wpf + C#
Nov 5, 2007Hi,
i want to create dynamic controls using wpf and c#.
can any body send me some code snippet.
for example:-
one of my window page have 100s of fields like textbox, checkbox and date controls in a group inside different grid and panel.
now if admin has made that amoung 100 fields user should be able to see only 20 or 30 fields.
how will i do so, just making the fileds invisible won't work, coz all the fields should be visible from top of the form,
means i want to push all the visible fields at the top.
what are the best way to do so.
need it urgent
thanx a lot
Create Dynamic Table From CSV
Apr 3, 2007Hi,
I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:
Time, Probe1, Probe2, Probe3, Probe4
Whereas the next month I can have something like this
Time, Probe2, Probe3, Probe1, Probe4
The "Time" column will always be on the left side, but the probes may vary in their placement.
I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.
Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?
Any help appreciated!
How To Create Dynamic T-SQL Clause
May 6, 2008Hi,
I have a project which is need dynamically t-sql clause...I've created interface for the create dynamic t-sql clause.
But the clause should get data from relational database. It needs inner join...so it's very hard to do it..
for example
lkpProduct
id product
1 Mouse
2 Keyboard
3 Modem
4 Monitor
main
id productid
1 4
2 1
3 2
4 3
5 4
You can get this table with 2 ways like this..
1. Select main.id, lkpProduct.product from main,lkpProduct where main.productid = lkpProduct.id
2. Select id, case productid when 1 then 'Mouse'.....end as product from main
mytable
id product
1 Monitor
2 Mouse
3 Keyboard
4 Modem
5 Monitor
My question is; how can i use CASE function dynamically ?? Mouse should get from lkpProduct....I do not want to write long table manually..
I hope it is clear for you..
Regards.
Dynamic Create Table
Oct 30, 2006Hi,
I need to create a table....whose column names should be dynamic...I wouldnt know in advance what the column names would be...These coulmn names are availabe in another table as rows...
Ex: TABLE_A Data
Column Names
Col1
Col2
Col3
Col4...
Some column Names are known already...I need help in writinf the Create table statement..
I need to write something like
Create table Table1
(SerialNo int,Name nvarchar(50)+ AS select * from TABLE_A +
Address nvarchar(500))....
Now the Table1 should look like
Serial No Name Col1 Col2 Col3 Col4 Address
Can some one please let me know how can i accomplish this...I know i need to write a Dynamic Query but dont exactly know ho to write it and execute it....
Thanks
How To Dynamic Create Bit Field On Sql
Apr 10, 2008
Hi
In Sqlserve we can create dynmic field on sql like:
Select '' as Name from Employee.
It creates a string field.
I want to how to create a Bit field in this way.
Regards
Deepak
How Do You Build A Dynamic WHERE Statement?
Mar 9, 2006I 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...
Need Help With Large Dynamic Sql Statement
Mar 16, 2006Is 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
Cannot Run Dynamic Sql Using 'USE DB_Name' Statement
Feb 4, 2002For 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
Dynamic Statement Needed?
Apr 23, 2014I 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.
Using A Dynamic Top Statement With A Cursor
Jul 20, 2005Help 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