Hi. I am using DBF files as sources for some tables in SQL Server. The problem is that the table names in the DBF files are not all the same (e.g. frame061, frame949). I want to know the names of the tables inside the DBF files. Is there a way to query the table names, something like "select table_name from information_schema.tables" in SQL Server? By the way, those DBF files came from FoxPro. Thanks!
View 4 RepliesHi,
Please advice me how to write query from table which is given as input(It is not hard coded)
Declare @varTableName varchar(30)
Select @varTableName = 'table_employee' -- table name
Select * from @varTableName
This gives me an error : Must declare the variable '@varTableName'.
The following are the output lines from my code which is constructing some T-SQL queries on the fly. The query highlighted in yellow is the problem query. The code upto the problem query is working correctly and I am able to see the output tables in the Query Analyzer
STEP 1 : Create 3 Tables In Dynamic T-SQL : Showing 3 Strings from Exec statement
Create Table ##Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80) )
Create Table ##OUT_Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80), KeywordID int, rank float )
Create Table ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val float)
Step 2: Retrieving A Value Into Another Global Table (table has only 1 row and 1 column)
Insert Into ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val ) Select IsNull( t , 0 ) from ##Test_word28July2007185548990201
(1 row(s) affected)
Step 3: I need the value (var_val) in ##STVR_FLOAT_Test_word28July2007185548990201
Update ##Out_Test_word28July2007194827580759 Set t = Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 where t > Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
(1 row(s) affected)
Problem Definition
Part 1
The update query is trying to retrieve a value in a dynamically constructed table. ##STVR_FLOAT_Test_word28July2007185548990201 (1 column var_val , 1 row)
Update <Table_Name> set t = @var_val where t > @var_val
The update query is simple, except I need to "SELECT" @var_val from the dynamic table
The @var_val is a float value.
I have outputted the contents of the table that is holding the @temp_val variable
It has the correct value and the table has only 1 row and 1 column.
WHAT IS THE SYNTAX OF A QUERY TO SELECT A SINGLE VALUE FROM A TEMP TABLE WITH ONLY 1 ROW OR COLUMN AND USE THE SELECTED VALUE AS A VARIABLE IN AN UPDATE STATEMENT
hi everyone,
joy mundy alluded in her webcast that it is possible to dynamically specify a table name in a parameterized ole db source query. is this true? if so, how can it be done?
I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2".
Is it possible to use that parameter as a part of the queried table name?
Something like (only for demonstration €“ doesn€™t work):
Select *
From table +myParameter + kkk
Thanks in advance!
I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)
[Code] .....
I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.
For Eg- @TableVariable
SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '
BUt it gives an error that @TableVariable must be declared
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
 - SQL Functions cannot use Exec to execute query strings.
 - SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
Â
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4,
[8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8,
[12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12,
[4/1/2008 12:00:00 AM] AS Month13, Engineer
FROM (SELECT Client, DollarsBilled, SlipDates, Engineer
FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM],
[6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM],
[12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
[code]....
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
I want to query a column of xml files in a table,
use mysql1
declare @bp xml
select @bp=xml
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from (
select
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as systolisch
from
BloodpressureMitSchema cross apply
@bp.nodes('/bp:content/bp:data/bp:events') as m(c))m
But with this "cross apply" I can only query all the values in one xml and repeat them. Is there something wrong at "declear"
I need to write a genric CSV importer. one set of CSVs define the formats of all teh other CSVs. The format of the second set of CSVs is not know at the time of creating this project/SSIS package.
Imagine having two sets of CSV files.
Each file in the first set of CSVs define the format of files in the second set of CSVs
The first set always have the same format. Each row of the CSV file defines a field with a Name and Type.
The first two columns of each row of in the first set of CSVs have a FormatName and index.
So a simple file may have:
Format1,1,Name,string
Format1,2,Surname,string
Format1,3,DOB,Date
Format1,4,Email,string
The second set of CSVs contains records that have to comply with the format define in the first set.
So in this aprticular case, a CSV file in the second set may have records such as:
Format1,John,Doe,2007-01-01,john@doe.com
Format1,Jane,Doe,2007-02-02,jane@doe.com
The problem I have is creating a generic SSIS package for this.
The first task, loading the first set of CSV file is fairly simple. The CSV format is known.
But the second task is a bit trickier.
Assuming I have SQL tables to load the data.
One 'Fields' record for each row in the CSVs from the fiirst set.
One 'Rows' record for each row in the CSVs from the second set,
One 'Values' record for each value in each row in the CSVs from the second set,
Something like:
TABLE Fields (
FieldID int IDENTITY,
FormatName varchar(100),
Position int,
ColumnName varchar(100),
ColumnType varchar(20) )
TABLE Rows (
RowID int IDENTITY )
TABLE Values (
ValueID IDENTITY,
RowID int,
FieldID int,
ColumnValue varchar(100) )
What would be the best/easiest SSIS approach:
- Dynamically create a SSIS package based on the content of each CSV fiel of the first set and execute that package for each file in the second set, selecting the correct package to execute (all records within a CSV file belong to the same format).
- Write a single SSIS package that iterates through the rows of the second CSVs, does a lookup for each value of each row to find the field name and make an insert into the DB
- Other SSIS method?
- Don't use SSIS to parse the CSV and call a custom C# task?
- Don't use SSIS at all ?
Thanks
I am new to SQL7 and I am trying to run a package that will import fixed text files in a folder and transform the data to a table. I am using a good example found in sywink.com for dynamically importing files in a directory, but I am getting an error message when there is more than one file in the folder. Also, I am able to display all the files during the loop process, but the file initially set for the source connection is the only one that transfers data. I have the close(Transform) connection checkbox option checked, and have tried other methods of closing the connection before the new source name is called, none have helped. Does anyone have any solutions to this type of problem? Or know of other methods used for my situation?
Thanks
James
Hi,
Trying to dynamically set the connectionstring property of the excel source.
This is what I enter instead of the hardcoded excel file paths:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=Excel 8.0;HDR=YES"
I get this error, every time I set the delay validation property of the dataflow tab to true.
Cannot detach from one or more processes.
The object invoked has disconnected from its clients
Do you want to terminate them instead?
Thanks
Hi All,
I have a challenge i am trying to overcome, hopefully soneone would have come across this issue before..
I am creating a DTS package that will be scheduled to run at a certain time everyday. A source folder exists that get a set of new files everyday.The DTS Package will then read each file and copy the data into a load table in my database the challenge is this:
I am trying to load files from a source folder into my load table, Within each file, the entires are in a specific format using pipes to seperate the data that goes into which column e.g
example of a file entry:
column1 | column2 | column3
data1 | data2 | data3
data1 | data2 | data3
data1 | data2 | data3
And now i am using DTS to specify the file format and map the cloumns as apprporiate to my table...all this is well and good, but my problem is each file has a different name as well as being timestamped, now how do i use DTS to specify the source folder, open each file sequentially and read (or more appropriate, copy the entries into my table, inserting new data from each file into my load table as well as overwriting old data in the load table from the files in the folder ?) is there a way in specifying your source folder in DTS rather than specifying the file in the Menu options (in the transformation data task properties )given, and or do i need to write a script for this(reading the file?)
can someone please give me a solution and how to approach this?
thanks in advance
Hi Evry one,
I Have Multiple Flat Files in Source Folder(They have Naming Conventions With Todays Date ex: Flatfile_20082204_1,Flatfile_20082204_2,Flatfile_20082204_3 ),
I need to Extract Each and Evry file by Dynamically, and Transform the Flat File then load that Flat file into the Destination Folder with Standard Prefix and Todays Date with a Sequence No ex:Flatfile_20082304_A,Flatfile_20082304_B, Flatfile_20082304_C
Please HELP Me
Thanks In Advance.
Hi,
is there any way to change the delimiter in a FlatFile Source Adapter dynamically? You can do that with the row delimiter but there is no expression for the column delimiter... Is there any other way besides changing the XML code?
Thanks,
I am working on FTP TASK in SSIS Package. i have to get files from FTP that file names are like 20141110.txt. i want to download any particular date file from ftp. How to i set expression in Remote path?
View 3 Replies View RelatedHi,
My scenario:
I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like
1) Multipying the source column by 100
2) Put an if condition for 2 source columns and then select one column to be copied into the destination.
I have the flat files schema with me and also all the transformations that are required.
Question:
Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?
Why I want this way?
In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.
Thanks for your time.
Regards,
$wapnil
Hi,
My Problem goes Like this....
I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project.
If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.
Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.
one more doubt is..
If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)
But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.
Hello,
I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.
My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible?
Regards
RH
HI,
As the source data of the cube is from MySQL, and the source data volume is more than 80M row per month, I have to build multiple partition in the cube, each partition contain only one month data, even though, the time to load data directly from MySQL is still too long, and because the mysql .Net provider and is not mature enough, the connection often break while loading, so I have to try to load from flat file which was exported from MySQL.
The Partition Processing Destination seems support this way, but, even it shows the partition name in component editor, it actually process the partition with partition ID, and there's not any way to change destination partition name for this component.
Unless I have to change the SSIS package every month, looks like it is impossible to make a smart ETL program that can dynamic create new partition with the ID and name as YYYYMM every month and load data directly from the flat csv file exported from big MySQL table.
Does anyone know how to load data from flat text, and also support dynamic destination partition name?
And I also find a bug in Partition Processing Destination, even 2005 SP2, if there's more than one cube in a SSAS database, and if two partition name in different cube is same, in component Editor, you can not set mapping to the second one with the same name, even you point to the second one, and click ok, the next time you open the editor, you will find it high light at the first one. And even it shows name in editor, it actually process with partition ID instead of partition name, this make it is not possible by change the partition name which need to process to a constant name, say currentMonth to force the component process it.
Thanks.
Jun
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. Â load data on newly created table. Â
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
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?
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
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?
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
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 RelatedHi
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