Dynamic Sql Statement
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Dynamic Cursor/ Dynamic SQL Statement
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 Replies !
View Related
Dynamic SQL Statement
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 Replies !
View Related
Dynamic Sql Statement
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 Replies !
View Related
Dynamic Sql Statement
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 "%sql server%" 3 like "%7%" I would like to generate a where clause like the one below: declare @st_sql (500) select @st_sql = 'where keyword like "%DBA%" and keyword like "%sql server%" and keyword like "%7%"' Can anyone help me the way to do it? number of rows in temp table varies anywhere from 1 to 15. (I know what I am trying to do is not a good sql coding practice.) Thanks
View Replies !
View Related
Dynamic SQL Statement Help
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 Replies !
View Related
Need Help With Large Dynamic Sql Statement
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 Replies !
View Related
How Do You Build A Dynamic WHERE Statement?
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 Replies !
View Related
Dynamic Update Statement
I have a table to process, with up to six data items. It has a matching record in another table which matches by an id field that will be update by this transaction record. Any combination of the items may have data in them, but if the field is blank we do not want to update the matching records data items, only ones that have data. I was thinking of creating a dynamice tsql statement created by some case statements, that check the length of the 6 data items. It sounds a little hairy but will probably work. Any better approaches?
View Replies !
View Related
Dynamic Select/Update Statement Possible?
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 Replies !
View Related
Dynamic Statement In Variable - Parseerror
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 Replies !
View Related
Building A Dynamic Sql Statement Into Stored Procedure
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 Replies !
View Related
Spooky Semi-dynamic Sql Update Statement
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 Replies !
View Related
SQL Statement For Querying Data With Dynamic Fields
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 Replies !
View Related
Dynamic Create Table Statement Or SELECT INTO
In 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.
View Replies !
View Related
Dynamic Column Names In Select Statement
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 Replies !
View Related
Looping Insert Statement With Dynamic Var Names...help...
672.1 I have a form that's sending 5 values, from form fields named "option1","option2" etc., to my SQL Server 7 DB. I want to loop through and insert each one into the table in my stored procedure. So I'm starting with: CREATE proc usp_upd_question @question_ID int output, @option1 varchar(255), @option2 varchar(255), @option3 varchar(255), @option4 varchar(255), @option5 varchar(255) as set nocount on declare @counter integer set @counter=1 while (@counter < 6) begin insert into question_options (question_ID, option_text) values (@question_ID, HERE'S MY PROBLEM!) set @counter=@counter+1 end Basically, I need some way to send the var ("option"+@counter) into the insert. I can get that string no problem, like set @thisoption='option'+cast(@counter as varchar(5)) ...but then it's a string, and I can't figure out how to get the DB to take that strong and look at it as though it were a variable. Anyone got any bright ideas? i'm sort of new at this SQL stuff. Thanks, Al
View Replies !
View Related
Dynamic CREATE TABLE Or SELECT INTO Statement
In 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.
View Replies !
View Related
Dynamic CASE Statement Based On List Of Dates
I have the following table of data. I need to take a date from a large table and do the following case:CASEWhen date < date(0) Then '0'When date between date(0) and date(1) Then '1'When date between date(1) and date(2) Then '2'When date >= date(3) Then '3'What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.thanksMilton Dates Table Date 4/1/2003 1/1/2006 4/2/2007 Fee Table Date Period Class Fee 1 Daily True 329 1 Half Day True 178 1 OT True 49 1 Hourly True 41 1 Daily False 156 1 Half Day False 86 1 OT False 27 1 Hourly False 19 2 Daily True 355 2 Half Day True 192 2 OT True 50 2 Hourly True 44 2 Daily False 171 2 Half Day False 92 2 OT False 28 2 Hourly False 21 3 Daily True 364 3 Half Day True 197 3 OT True 51 3 Hourly True 45 3 Daily False 175 3 Half Day False 94 3 OT False 29 3 Hourly False 21
View Replies !
View Related
Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View Replies !
View Related
Need Info About Dynamic Reporting (Read Problem Statement, Inside)
Hi All! I have a specific requirement. I have to generate a report on the fly. The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more. From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly. So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time. I hope i have explained very clearly. I would be grateful for your contributions. Thanks
View Replies !
View Related
Incorrect Syntax Near The Keyword 'EXEC' (Dynamic Sql Statement Error)
Following is the stored procedure iam trying to create.Here i am trying to First create a table with the table name passed as parameter Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows. Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching. This table would be further used with cursor. Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated. CREATE PROCEDURE [dbo].[sp_try] @TempTable varchar(25) AS DECLARE @SQL nvarchar(MAX) DECLARE @SQLINSERT nvarchar(MAX) BEGIN --create temp table SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) + '( ContactName varchar (40) NOT NULL , ContactId varchar (30) NOT NULL , ContactrMessage varchar (100) NOT NULL, )' EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable SELECT @sql= 'Select * from table' SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable ) SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable
View Replies !
View Related
Syntax Error Converting Datetime From Character String In Dynamic SQL Statement
Hi, I'm getting the "Syntax error converting datetime from character string." for the below query. I have some request like this query and need to know the correct sql statement for this to avoid this error. use northwind go declare @reqdate datetime ,@shipdate datetime set @reqdate='1996-05-05' set @shipdate='1996-06-05' declare @cmd varchar(1000) set @cmd='select * from orders where requiredate >='+@reqdate+' AND shipdate >='+@shipdate exec @cmd
View Replies !
View Related
Using A &"dynamic Top&" Statement With A Cursor
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 Replies !
View Related
Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Hi Craig/Kamal, I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative. Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information. We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user. I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns. Your help in this respect is highly appreciated! Thanks, Hi Anthony, I am glad the Web cast was helpful. Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using. I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 Thanks Craig Guyer SQL Server Reporting Services
View Replies !
View Related
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly. So, is this possible using data driven subscriptions? Scenario is: 1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user. Any tips on how to get this working? Thanks Mark Smith
View Replies !
View Related
Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?
If anyone could confirm... SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter. E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere. First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided. However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change. Question: I thought the filters would remain dynamic and be applied on each sync? I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not! Question: I wonder why if parent records are supplied, why not child records? If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server... Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View Replies !
View Related
Mixing Dynamic SQL With Non-Dynamic In Stored Proc
I have a Stored Procedure for processing a Bill of Material. One column on the Assembly Table is a Function Name that contains some busniess rules. OK, now I'm doing a Proof of Concept and I'm stumped. Huuuuh! I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function. Note: The function just returns a bit. So; here's what I had in mind ... if isnull(@FnNameYN,'') <> '' exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output CREATE PROCEDURE dbo.spinb_CheckYN @FnNameYN varchar(50), @InvLineID int, @FnBit bit output AS declare @SQL varchar(8000) set @SQL = ' if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1 set @FnBit = 1 else set @FnBit = 0' exec (@SQL) GO Obviously; @FnBit is not defined in @SQL so that execution will not work. Server: Msg 137, Level 15, State 1, Line 4 Must declare the variable '@FnBit'. Server: Msg 137, Level 15, State 1, Line 5 Must declare the variable '@FnBit'. So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable? My many thanks to anyone who can solve this riddle for me. Thank You! Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows: Hopefully a better solution comes to light. ------ Vertical Build1 - Std Vanes ----------- if @FnNameYN = 'fnb_YN_B1_14' BEGIN if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END ------ Vertical Build1 - Scissor Vanes ----------- if @FnNameYN = 'fnb_YN_B1_15' BEGIN if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END . . . etc.
View Replies !
View Related
Dynamic Source And Dynamic Destination
I have a requirment which i have partly accomplished , but could not get through completely i have a file which comes in a standard format ending with date and seq number , suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on . then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder what i have taken apprach is script task select source file --------------------> data flow task------------------------------------------> script task to destination file dataflow task -------------------------> does count and copy in delimited format what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task . but cannot work the dynamic pick of a source file. please advise with your comments or solution you have
View Replies !
View Related
Dynamic Columns For Dynamic SQL
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007. Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select. I have come to understand that a dynamic anything is a moving target! Please advise.
View Replies !
View Related
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I 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 Replies !
View Related
Using Conditional Statement In Stored Prcodure To Build Select Statement
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 Replies !
View Related
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
View Related
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
View Related
Compiler Is Not Recognizing My Using Statement For SglConnection Statement
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code. Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message. Compilation Error Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error: Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 1 <asp:sqldatasource runat="server"></asp:sqldatasource> 2 <%@ Page Language="C#"%> 3 using System; 4 using System.Data; 5 using System.Collections; 6 using System.Data.SqlClient; 7 8 <script runat=server> 9 10 protected void Page_Load(object o, EventArgs e) 11 { 12 ObjectDataSource dsa; // This works no problems from the compiler here 13 SqlDataSource ds; // This works no problems from the compiler 14 15 if (IsPostBack) 16 { 17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text)) 18 { 19 instructions.Text = "Congratulations, your authenticated!"; 20 instructions.ForeColor = System.Drawing.Color.Red; 21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'"; 23 } 24 else 25 { 26 instructions.Text = "Please try again!"; 27 instructions.ForeColor = System.Drawing.Color.Red; 28 } 29 } 30 31 } 32 33 bool AuthenticateUser(string username, string password) 34 { 35 // Authentication code goes here 36 37 }
View Replies !
View Related
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
View Related
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
View Related
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
View Related
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
View Related
How To Read SQL Statement In T-SQL Statement Task?
hi all, after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task. i open the DTSX in vs2005, but i can open to view the SQL statement. in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task. please help. thanks a lot
View Replies !
View Related
|
TRACKER
