Regarding Dynamic Query...
May 15, 2005
Hi,
I have basic design question regarding dynamic query,
When we have to build a dynamic query (which has table name also as an input parameter),
->Is it better to write a stored procedure ..?
or
->directly specify the dynamic query and set the command type as text in .NET code...?
I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..
Please through some light on this,
TIA
View 2 Replies
ADVERTISEMENT
Jun 20, 2006
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!
View 5 Replies
View Related
Sep 14, 2007
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?
View 13 Replies
View Related
Jun 9, 2008
Hi..
I want to change the SQL Query Dynamically ....
i have attached the Source code.....1st Page: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ConsIndex.aspx.cs" Inherits="Cons_ConsIndex" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="sqldatasource1" Width="80%" runat="server"> <ItemTemplate> <table align="left"> <tr> <td> <a id="href1" href="consA-Z.aspx?cons=A" target="_self">A </a> </td> <td> |</td> <td> <a id="A1" href="consA-Z.aspx?cons=B" target="_self">B </a> </td> <td> |</td> <td> <a id="A2" href="consA-Z.aspx?cons=C" target="_self">C </a> </td> <td> |</td> <td> <a id="A3" href="consA-Z.aspx?cons=D" target="_self">D </a> </td> <td> |</td> <td> E</td> <td> |</td> <td> F</td> <td> |</td> <td> G</td> <td> |</td> <td> H</td> <td> |</td> <td> I</td> <td> |</td> <td> J</td> <td> |</td> <td> K</td> <td> |</td> <td> L</td> <td> |</td> <td> M</td> <td> |</td> <td> N</td> <td> |</td> <td> O</td> <td> |</td> <td> P</td> <td> |</td> <td> Q</td> <td> |</td> <td> R</td> <td> |</td> <td> S</td> <td> |</td> <td> T</td> <td> |</td> <td> U</td> <td> |</td> <td> V</td> <td> |</td> <td> W</td> <td> |</td> <td> X</td> <td> |</td> <td> Y</td> <td> |</td> <td> Z</td> <td> |</td> </tr> </table> </ItemTemplate> </asp:FormView> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="consid" DataSourceID="sqldatasource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="consid" Visible="False" /> <asp:TemplateField SortExpression="consid"> <ItemTemplate> <p align="left" style="font-family: Arial; font-size: small;"> <img src="../images/arrow.gif" border="0" width="10" height="11" /> <a href="Consshorttitle.aspx?<%# Eval("consid") %>"> <%# Eval("consname") %> </a> </p> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="sqldatasource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM consindex Order by consname"> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content>--------------------------------------------in this file i am passing the value "A" to (top of the page) "Z" etc... -------------------------------------------2nd Page <%@ Page Language="C#" AutoEventWireup="true" CodeFile="consA-Z.aspx.cs" Inherits="Cons_consA_Z" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="consid" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="consid" Visible="False" /> <asp:TemplateField SortExpression="consid"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> </td> <td align="left" width="75%"> <%# Eval("consname")%> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM consindex WHERE consname LIKE '@cons%'"> <SelectParameters> <asp:QueryStringParameter Name="cons" QueryStringField="cons" Type="int32" /> </SelectParameters> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content>-----------------------------The content which i bold in the 2nd file .. wanted dynamically...... please give me a solution ... -----------------------------I want to take the letters starting with A, B, C to Z dynamically in the Second Page,.... Thanks in advance
View 6 Replies
View Related
Nov 9, 2004
I want to create a report which uses a query that has a dynamic "in" list.
something like
select * from employee where employeeID in (@empid)
when I send @empid = 1,2,3 , it shows a compiler error ,
"syntax error converting the nvarchar value '1,2,3' to a column of data type int'.
any workarounds?
View 3 Replies
View Related
Jul 19, 2005
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?
View 3 Replies
View Related
Jan 4, 2001
Hi
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 ?
Thanks,
Manoj
View 1 Replies
View Related
Oct 19, 2004
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!
View 6 Replies
View Related
Jun 20, 2008
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.
Thanks
Swati
View 6 Replies
View Related
Oct 7, 2006
month wise production
Format that I want
item codenameJulyAugSepOctNovDecjan
1002pvc resin 3020115060140
3501SWR pipe566045801002020
I create crosstab procedure as follows
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'
Output
GrnNo12249
1220NullNull20
2Null20Null10
3NullNull300Null
4NullNull10Null
I could not understand the error.
What will I do to get the format I want?
shohan
View 1 Replies
View Related
Jan 30, 2008
how to create dynamic query and use it as a
table. Like the way we use OPENROWSET or
OPENQUERY?
like...
select * from sp_executesql('select * from mytable')
Thanks..
Sandeep.
View 8 Replies
View Related
Sep 17, 2006
Hello
I have a problem with writing a query.Let me give an example:
Table:
ColA , ColB , ColC , Col1 , Col2 , Col3 , Col4 , Col5
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.
How can I do this?
Thanks.
View 6 Replies
View Related
Jul 19, 2007
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...
example:
@TableName VARCHAR(20),
SELECT * FROM @TableName
is it possible?
View 6 Replies
View Related
Jul 4, 2007
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
View 4 Replies
View Related
Nov 28, 2007
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+'')
View 1 Replies
View Related
Jun 4, 2002
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 ...???
Any help appriciated...
View 1 Replies
View Related
Mar 11, 2008
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..
here is my table
StartDate | EndDate
---------------------
20071231 | 20080306
here is my function knowing that i'm passing "Start" as a variable:
Code:
ALTER FUNCTION tu_efgn_int.Get_StartEndDates(@DateType varchar(10))
RETURNS varchar(8)
AS
BEGIN
DECLARE @vDate varchar(15)
Set @vDate= (Select @DateType + 'Date' From dbo.ps_tbl_SelectedDates)
RETURN @vDate
END
@vDate always returns "StartDate" as result instead of 20071231 why is that ?
View 2 Replies
View Related
Jan 5, 2007
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'
View 4 Replies
View Related
Nov 18, 2011
@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.
View 2 Replies
View Related
Apr 17, 2008
I have to break dynamic query into two as it was more than 4000 characters long.
I have to use Execute(@nsql_1 + ' ' + @nsql_2) now
how can I use two parameters to use sp_executesql?
EXECUTE @error = sp_executesql @nsql, N'@min_date SMALLDATETIME,@max_date SMALLDATETIME',@min_date,@max_date
Thanks for help....
View 10 Replies
View Related
May 13, 2008
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
END
View 4 Replies
View Related
Sep 10, 2014
I do have a table type as input
---------------------------
num|columnname|value
---------------------------
1|Name|3X5900
2|employeeID|null
3|JOD|null
4|Address|null
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,
[Code] ....
View 1 Replies
View Related
May 29, 2007
I have created a connection in connection manager.
using expressions i have declared a connection string
"Data Source=" + @[User::servername] + ";Initial Catalog=" + @[User::catalog] + ";Provider=SQLNCLI.1;
Integrated Security=SSPI;
Auto Translate=False;"
when i click 'evaluate expression ' it just displays
Data Source=;Initial Catalog=;Provider=SQLNCLI.1;
Integrated Security=SSPI;
any anyon etell me if iam doing any mistake
View 5 Replies
View Related
Feb 27, 2008
Hi,
I am using a simple if staement in a dynamic sql query what is its synax.
if @docType ='Doctor'
begin
select * from @tablename
end
Ranjeet Kumar Singh
View 3 Replies
View Related
Feb 13, 2006
I'm having a problem in getting a dynamic query in a sp working and thecode is below so can someone please help me? It would be very muchappreciated.ThanksGirogio--------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[Search_Profile]@Country NVARCHAR(100) = null,@County NVARCHAR(100) = null,@Town NVARCHAR(100) = null,@AType bit,@PageIndex int,@NumRows int,@UsersCount int OutputASBEGINDECLARE @where_clause NVARCHAR(500);IF @Country IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCountry = "' + @Country +'"'ENDIF @County IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aCounty = "' + @County + '"'ENDIF @Town IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND aTown = "' + @Town + '"'ENDIF @AType IS NOT NULLBEGINSET @where_clause = @where_clause + ' AND Independent = "' +Convert(NVARCHAR, @AType) + '"'ENDDECLARE @Query1 NVARCHAR(1000);SET @Query1 = 'SELECT @UsersCount=(SELECT COUNT(*) FROM CustomProfileWHERE aActive = 1 ' + @where_clauseexec(@Query1)DECLARE @startRowIndex int;SET @startRowIndex = (@PageIndex * @NumRows) +1;DECLARE @Query2 NVARCHAR(1000);SET @Query2 = 'WITH UsersProfiles as (SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,t.apubName, t.aCounty, t.aTown, u.UserNameFROM CustomProfile t, vw_aspnet_Users uWHERE t.UserID = u.UserID AND aActive = 1 ' + @where_clauseDeclare @Query3 NVARCHAR(1000)SET @Query3 = 'SELECT UserId, apubName, aCounty, aTown, UserNameFROM ' + @Query2 +' WHERE Row BETWEEN ' + Convert(NVARCHAR, @startRowIndex) + ' AND ' +Convert(NVARCHAR, @startRowIndex+@NumRows-1)exec(@Query3)END
View 2 Replies
View Related
Jul 20, 2005
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!
View 2 Replies
View Related
Mar 5, 2008
Show me example of dynamic SQL query if possible
thank you
View 5 Replies
View Related
Apr 18, 2006
I want to get the count of rows in the table which match the status. I am writing dynamic query for it..
Create Procedure Dyn_Get_CountByStatus
(
@TableName varchar(200),
@Status int
)
as
Begin
Declare @strQuery varchar(500)
Declare @count int
set @strQuery = 'select count(*) from '+@TableName + 'where status=' + @Status
set @count =exec(@strQuery)
return @count
End
GO
This query is not working. How can get the desired result using dynamic query
View 3 Replies
View Related
May 21, 2008
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
Thank you.
Gabriele
View 8 Replies
View Related
Dec 24, 2006
I make a sp in mt sql server,
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)
how can I do it?
View 1 Replies
View Related
Feb 26, 2008
All,
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
set @count = count1
print @count
print 'here'
View 7 Replies
View Related
Sep 21, 2007
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.
View 5 Replies
View Related
Aug 16, 2007
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..
View 3 Replies
View Related
