Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results: Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _ "[Table].[Name] = @Name)" But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!
I am having trouble wrapping my head around some dynamic queries. I have x number of queries stored in a table. Each row in this table has a From, Join, and Where column. So, for x=3, I can run the query from each row so that I may have Q1: (1, 2, 4, 5, 7, 8) Q2: (1, 3, 5, 7, 9) Q3: (2, 4, 6, 8, 10) I need to use these queries to generate a shared table: |-------------------| | ID | Q1 | Q2 | Q3 | | 1 | 1 | 1 | 0 | | 2 | 1 | 0 | 1 | | 3 | 0 | 1 | 0 | | 4 | 1 | 0 | 1 | | 5 | 1 | 1 | 0 | | 6 | 0 | 0 | 1 | | 7 | 1 | 1 | 0 | | 8 | 1 | 0 | 1 | | 9 | 0 | 1 | 0 | | 10 | 0 | 0 | 1 | |-------------------| I'm not sure the best way to do this. I think that doing it on the asp.net side will be easier than in t-sql, although I am exploring both possibilities. Any suggestions?
I have an array list of Branch_ID's; i need to select all records from 2 tables that have any of the branch id's in that array associated with them. How would I go about doing this?
I have two options two write queries one Static & one Dynamic. I wanted to know which one will be good.
Example
if @x = 1 Select * From Customers order by CustomerID Else IF @x = 2 Select * From Customers Order by CustomerName Else IF @X = 3 Select * From Customers Order by city else.. ...
Against this Set @Strsql = "Select * From Customers order by " + @Orderby Exec (@Strsql)
Which will have better performance ? Is SQL Stores query plans for static queries with all if statements clause or it remains as good as dynamic ?
I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z row2 x b z row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!
Hi I am new to sql. I have to run a query in a manner that I can see customer name and their photo dynamically? I already have table created where names and customers images are there.Please help.
create procedure up_CrossTab (@SelectStatement varchar(1000), @PivotColumn varchar(100), @Summary varchar(100), @GroupbyField varchar(100), @OtherColumns varchar(100) = Null) AS /* Inputs are any 1000 character or less valid SELECT sql statement, the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1 */ set nocount on set ansi_warnings off
declare @Values varchar(8000); set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'') /* An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2 */ create table #temp (Tempfield varchar(100))
insert into #temp exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' + replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' + Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield ) from #Temp order by Tempfield
drop table #Temp /* Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3 */ exec ( 'select ' + @GroupbyField + @OtherColumns + @Values + ' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off set ansi_warnings on GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ', 'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Ok.I must write a SP and it gets a parameter,say @param. if @param=1 then in the select statement I will select Col1,if @param=2 then I will select Col2 and so on.
Good Day to all, I just started using SQL Server 2005 and didn't have any experience with other DB... Is it possible to make a dynamic query? I would like to make a stored procedure where the table name from where it will select/insert/update data is user inputed... if it's possible, can someone please guide me.. thanks in advance...
I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @tblNode AND tblCorp = @tblCorp AND tblService = @tblServiceThe @tblNode, @tblCorp, @tblService are all pulling from 3 separate drop down controls. Ok easy enough.However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?I guess I am looking to see if a wild card can be used so tblNode = % or something. The server is MSSQLThank you
Hii I am Varun i have a problem with the dynamic stored procedure This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1 (@TeacherID_OnLeave numeric(9), @DateFrom datetime ,@DateTo datetime , @UserID numeric(9) ) AS declare @flag as numeric(9) declare @year as varchar(4) set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0) if( @year=null or len(@year)=0) set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+') begin insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+'); end') else set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave) return @flag --exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+' --') --return @flag --exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
I created procedure to execute dynamic query. if my query is Q1='select empno from EMP;' it works fine but it does not work with query Q2='select empno into @v_empno from EMP where empno = :xemp'; I am using exec sp_excutesql @Q2, @xemp =21;
Can we not use INTO in dynamic query....? it gives me syntex errors for @v_empno ...???
Good afternoon, i'm new to Functions on the SQL server
I'm trying to create a dynamic query that would select the the column passed to the function from a certain table
my table called selected_Date, and has StartDate, and EndDate columns
when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..
I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.
If I try to run it with QA, I get the following message: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'FCST'. UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...
However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.
What could I be missing?
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst, Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst, Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst, Dec_07=Dec_fcst FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT, Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst, Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst FROM PLAN_SHIP INNER JOIN BOD_HEADER ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX WHERE (SCEN_ID = 1) AND ([Year] = 2007) ) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX WHERE OPSPLAN.SRCPLAN = 'SHIP'
@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.
So only if it returns single record then I have to store it otherwise convert to xml before storing.
1) If it returns 1 record(1row and 1 column) then save as it is. 2) if it returns a row with more than 1 columns then convert to xml before saving. 2) if it returns data rows then convert to xml before saving.
The following dynamic query returns a list of tables some of which do not have records in them. Can someone help me out, I am trying to exclude the tables with no records returned? Also, I want to exclude tables where there is not a rn_create_user or rn_edit_user defined in the table?
DECLARE @TableName sysname, @Sql varchar(8000)
SELECT @TableName=MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHILE @TableName IS NOT NULL BEGIN SELECT @Sql='select o.* from ' + @TableName + ' o where not exists(select * from users u where (u.users_id = o.rn_create_user) or (u.users_id = o.rn_edit_user) )'
EXEC (@Sql)
SELECT @TableName=MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME>@TableName
From this I have to form the where clause like "Name like'3X5900' ".This will be dynamic , as the search may vary on the next call for search.
How I have to implement this dynamic query in the below mentioned code.I have add the dynamic query where i have mentioned as '---- Where clause has to be added '. Sometime the table input will be having all the value for search. How I can implement this in my query with good performance.
DECLARE @Where varchar(4000); Select @where = 'v.Name LIKE ''3X5900''' SELECT * from (SELECT Row_number()Over(Order By V.ProjectId) Rownum, v.Firstname,
I'm looking for a way to transform the contents of n source tablesinto a single destination table. This by itself is no problem.However, the name of the source tables change, so I'll need to basethe transform task on a global variable that I can update via externalcode. Not sure how to do that. I'm ok with executing the package 10times if there's 10 source tables.The last unknown piece is modifying the query used for the transform.There are 10 columns in the source table, but there are 12 columns inthe destination table. I must provide the 2 missing columns. They willsimply contain a year and month, ie. 05 2003.I'm taking a bunch of source tables (for a given month and year) androlling them together into one destination table, and carrying overthe month and year. I assume the month and year would also be globals.But I'm not sure how to incorporate them into the transform task sinceit wants strict SQL syntax.Any help is appreciated, thanks in advance!
I need to implement a "dynamic" select query, in which I want that the type and the number of columns are variable/changeable. For example using the parameters of a stored procedures. I would have a client code (written in VB .NET) in which I would choose the columns that I want to display in a DataGridView.
How should I modify the SELECT line or the SP in general to solve the problem??
The code I need to modify is this:
Code Snippet CREATE PROCEDURE uspSelect1 @iDataInit datetime = '01 gen 1753', @iDataEnd datetime = '31 dic 9999', @iArticle nvarchar(50) = N'%', @iMachineNum smallint, @iTypeMaterial nvarchar(50) = N'%', @iNominalCount float, @iOperator nvarchar(50) = N'%', @iCustomer varchar(50) = N'%', @iComments nvarchar(1000) = N'%', @iLanguage nvarchar(50) = N'%' AS SELECT i.IDsample, i.Date, i.Article, i.MachineNum, i.TypeMaterial, i.NominalCount, i.Operator, i.Customer, i.Comments, i.Language, r.Um, r.CVm FROM Identification i JOIN Reports r ON (i.IDsample = r.IDsample) WHERE i.Date BETWEEN @iDataInit AND @iDataEnd AND i.Article LIKE @iArticle AND i.MachineNum = ISNULL(@iMachineNum, i.MachineNum) AND i.TypeMaterial LIKE @iTypeMaterial AND i.NominalCount = ISNULL(@iNominalCount, i.NominalCount) AND i.Operator LIKE @iOperator AND i.Customer LIKE @iCustomer AND i.Comments LIKE @iComments AND i.Language LIKE @iLanguage ORDER BY i.IDsample
the SP get one parameter, its the WHERE, and then I execute the query,
here is my SP: ALTER PROCEDURE [dbo].[rptEventToPsy] @strSQL nvarchar(4000)='' as DECLARE @SQLString NVARCHAR(4000); SET @SQLString = N'SELECT dbo.tbl_CA_meeting.userID, dbo.tblUsersName.OrdName, COUNT(dbo.tbl_CA_event.itemInPackageID) AS Expr1, dbo.tbl_CA_itemInPackage.itemInPackageName, dbo.tbl_CA_package.packageName FROM dbo.tbl_CA_meeting INNER JOIN dbo.tbl_CA_event ON dbo.tbl_CA_meeting.eventID = dbo.tbl_CA_event.eventID INNER JOIN dbo.tbl_CA_itemInPackage ON dbo.tbl_CA_event.itemInPackageID = dbo.tbl_CA_itemInPackage.itemInPackageID INNER JOIN dbo.tbl_CA_package ON dbo.tbl_CA_itemInPackage.packageID = dbo.tbl_CA_package.packageID INNER JOIN dbo.tblUsersName ON dbo.tbl_CA_meeting.userID = dbo.tblUsersName.UserId INNER JOIN dbo.tbl_CA_mishmeret ON dbo.tbl_CA_meeting.mishmeretID = dbo.tbl_CA_mishmeret.mishmeretID '; SET @SQLString = @SQLString + @strSQL ; SET @SQLString = @SQLString + ' GROUP BY dbo.tbl_CA_meeting.userID, dbo.tblUsersName.OrdName, dbo.tbl_CA_itemInPackage.itemInPackageName, dbo.tbl_CA_package.packageName'; EXEC sp_ExecuteSql @SQLString return;
my problem is in the Reports, I cant to connect the data to the fileds, (the value is empty)
I need help on the procedure below. this procedure gets intput table_name and do a row count. I want to print out the result from the Dynamic query within the procedure.
Thanks in advance
create PROCEDURE [dbo].[SRC_sp_Batch_test]
@table_name as varchar(50)
AS
DECLARE @execstr2 as varchar(1000)
declare @count as varchar(10)
set @execstr2 = 'select count(*) as count1 from ' + @table_name
exec (@execstr2)
-- want to print out the records count from executing @execstr2
In a SP I need to know if certain records already exist but the query is parameter dependent so I can't code IF EXISTS (SELECT ...) because the proper select must be calculated. Using EXEC (@CalculatedQuery) IF @@ROWCOUNT = 0 Puts the results of @CalculatedQuery into my SP result set. This is highly undesireable.
Hi, I need to create a stored procedure, which needs to accept the column name and table name as input parameter, and form the select query at the run time with the given column name and table name.. my procedure is, CREATE PROC spTest @myColumn varchar(100) , @myTable varchar(100) AS SELECT @myColumn FROM @myTable GO This one showing me the error, stating that myTable is not declared.. .............as i need to perform this type of query for more than 10 tables.. i need the stored procedure to accept the column and table as parameters.. Plese help me?? Is it possible in stored procedure..
