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
On my SP3 db_name(0) and db_name(1) are returning the same thing. Can anybody else try this? Beta of Yukon is doing the same thing...But the following code returns 1: select db_id(db_name(0))
View 3 Replies View RelatedHi,
I want to submit db name as as default parameter when calling procedure WITHOUT using variables
Example
use master
go
create procedure get_caller_db @s sysname = DB_NAME() --produce error
as
select @s as [caller db name]
go
use mySuperDB
exec master.dbo.get_caller_db
--
caller db name
--------------
mySuperDB -- result I want
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.
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 ':'.
Hi Everyone,All the very best for 2004!!i need urgent help with this problem, the users are about to skin mealive!!we have an access front end with linked to sql server 2k tables.when a user tries to insert a record into one of the tables it"randomly" returns a generic ODBC error and fails to save. on otheroccasions the same record will save.a trace was applied and the following select seemed to appear rightafter the insert statement whenever it failed:select substring('NY',status/1024&1+1,1) from master..sysdatabaseswhere name=DB_NAME()i had a look at other articles in the groups re this select statement,but could not find a clear answer.i have tried the insert statements as both SQL pass throughs and justplain docmd.runsql'scan someone help me with the following:* what is the purpose of the select?* what other investigations can i do to get more info on why thisshould be happening?* how can i stop it?the table i am doing the inserts into is showing as have a numericdata type field in sqlserver, but the linked table shows this numericfield as text - could this be the problem?? this field is not used inthe insert statement.i could not find any references in the MS knowledge base.any and all help would very gratefully received.Edwinah63
View 2 Replies View RelatedI 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 RelatedI 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
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
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?
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...
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
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.
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
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
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
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 RelatedI 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
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 RelatedHi,
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)
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
)
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.
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.
I'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
Hi...how can I write a dynamic sql statement to insert x number of child records? Thx in advance
View 4 Replies View RelatedI 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
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Insert
('+@columns+',KickoutID) Values('+@columns+',1);
Dynamic code:
Declare @columns as nvarchar(max);
Declare @InvSQL as nvarchar(max);
SELECT @columns =
STUFF ((
SELECT ', [' + name + ']'
FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
[Code] ....
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 4 Replies View RelatedI am working on to convert my static Store procedure to Dynamic.
I have created a Store procedure with Merge statement which is inserting new record and updating existing record.
This SP I will use in SSIS Insted of Data Flow Task I will run in Execute SQL Task.
Now my biggest problem is I dont know how to convert static code toi dynamic
Below is my Store procedure code.
As you can see my Source Query
I have a filemaster table as shown below which consist of Input filename,Source table ,Destination table and BBX expression.
Input_FilenameSourceTableName DestinationTableName BBxKeyDerExpr
CCTFB ImportBBxCctfb ArchiveBBxCctfb SUBSTRING(Col001,1,6)
CEMXR ImportBBxCemxr ArchiveBBxCemxr SUBSTRING(Col001,1,10)
In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.
Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.
Lets consider @File_name is the variable in package which store the file name
if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.
Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.
CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed .
(SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.
[Code] ....
I am able to get inserted and updated rowcount, but not able to get the matching records count.
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
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
I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:
CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME
[Code] ...
Below is Our Sample Table Data.
Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015
How to write Pivot Satement to get Oupt like below:
1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12
1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4
1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17