Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.c..._procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.c..._procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
Jim
CREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>
|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
>
sql

Dynamic Query How To?

Hello,
I have a website that I designed with Visual Web Developer and SQL 2005
Express. Everything works fine, except that I had to write a seperate query
for each product category which is currently around 10. I expect this to
grow as we add new products and I can see this getting hard to manage in the
future.
Is there a way to write a dynamic query that will take a single parameter
(SeriesID) and run it when the page is loaded? I'm familiar with Access, but
new to SQL 2005 Express.
Thanks
JimCREATE PROCEDURE dbo.GetCategoryItems
@.CategoryID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT <columns> FROM <table> WHERE CategoryID = @.CategoryID ORDER BY
<?>;
END
GO
Now just pass the categoryID into the stored procedure.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Hi
Yes . You need to write stored procedure that accept input parameter and
generate the out put based on the parameter passed in
this site might you to start with
http://www.sql-server-performance.com/tn_stored_procedures.asp
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Jim" <jim@.gordonferon.com> wrote in message
news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hello,
> I have a website that I designed with Visual Web Developer and SQL 2005
> Express. Everything works fine, except that I had to write a seperate
> query for each product category which is currently around 10. I expect
> this to grow as we add new products and I can see this getting hard to
> manage in the future.
> Is there a way to write a dynamic query that will take a single parameter
> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
> but new to SQL 2005 Express.
> Thanks
> Jim
>|||Thanks for the information and the link. This looks like exactly what I
need.
Jim
"Vt" <vinu.t.1976@.googlemail.com> wrote in message
news:eBfoom3rHHA.3884@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes . You need to write stored procedure that accept input parameter and
> generate the out put based on the parameter passed in
> this site might you to start with
> http://www.sql-server-performance.com/tn_stored_procedures.asp
>
> Regards
> Vt
> Knowledge is power;Share it
> http://oneplace4sql.blogspot.com
> "Jim" <jim@.gordonferon.com> wrote in message
> news:%23BhDem2rHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hello,
>> I have a website that I designed with Visual Web Developer and SQL 2005
>> Express. Everything works fine, except that I had to write a seperate
>> query for each product category which is currently around 10. I expect
>> this to grow as we add new products and I can see this getting hard to
>> manage in the future.
>> Is there a way to write a dynamic query that will take a single parameter
>> (SeriesID) and run it when the page is loaded? I'm familiar with Access,
>> but new to SQL 2005 Express.
>> Thanks
>> Jim
>>
>
>

Dynamic Filter

Hi,
I have a Reporting Services report that I would like to add a dynmaic filter
to. I want to allow the users to filter based on two seperate fields (or
view all data). Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. ignore those with null values). The same would hold true if the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank), I don't want to limit the data.
Is this possible? I would prefer to accomplish this without using dynamic
sql.
Let me know if you have any questions...
Thanks for the help!
-David.are you using embedded sql code, or a stored procedure call?
"David" wrote:
> Hi,
> I have a Reporting Services report that I would like to add a dynmaic filter
> to. I want to allow the users to filter based on two seperate fields (or
> view all data). Example: if the user selects "Field1" from the filter
> drop-down, I only want to display records in the dataset that have a value in
> Field1 (i.e. ignore those with null values). The same would hold true if the
> user would select "Field2". If the user does not select a value for this
> parameter (i.e. it is blank), I don't want to limit the data.
> Is this possible? I would prefer to accomplish this without using dynamic
> sql.
> Let me know if you have any questions...
> Thanks for the help!
> -David.|||Embedded sql...
"Carl Henthorn" wrote:
> are you using embedded sql code, or a stored procedure call?
> "David" wrote:
> > Hi,
> >
> > I have a Reporting Services report that I would like to add a dynmaic filter
> > to. I want to allow the users to filter based on two seperate fields (or
> > view all data). Example: if the user selects "Field1" from the filter
> > drop-down, I only want to display records in the dataset that have a value in
> > Field1 (i.e. ignore those with null values). The same would hold true if the
> > user would select "Field2". If the user does not select a value for this
> > parameter (i.e. it is blank), I don't want to limit the data.
> >
> > Is this possible? I would prefer to accomplish this without using dynamic
> > sql.
> >
> > Let me know if you have any questions...
> >
> > Thanks for the help!
> >
> > -David.|||An example of your code would be helpful. when you say "Filter", are talkign
about reducing the size of your result set by using the defined parameters in
a where clause. it looks like you want to return different columns from a
table depending on which parameters are picked. Which is it? both?
if you are talking columns, use the visibility expression to hide the
unwanted columns based on the parameter value. if you are talking where
clause, then you can get around using dynamic sql by using a case stmt in the
stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
hth!
"David" wrote:
> Embedded sql...
> "Carl Henthorn" wrote:
> > are you using embedded sql code, or a stored procedure call?
> >
> > "David" wrote:
> >
> > > Hi,
> > >
> > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > to. I want to allow the users to filter based on two seperate fields (or
> > > view all data). Example: if the user selects "Field1" from the filter
> > > drop-down, I only want to display records in the dataset that have a value in
> > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > user would select "Field2". If the user does not select a value for this
> > > parameter (i.e. it is blank), I don't want to limit the data.
> > >
> > > Is this possible? I would prefer to accomplish this without using dynamic
> > > sql.
> > >
> > > Let me know if you have any questions...
> > >
> > > Thanks for the help!
> > >
> > > -David.|||If a user selects one of the filter criteria, I want to only show those
records that have a value in that field. I do not want to change the columns
that are returned. In effect, it would reduce the size of the dataset.
However, I just want to filter the data not exclude it from the dataset (to
reduce round-trips to the database). Also, the report parameter will contain
the field name for the users to select from (not the value).
Example: if the user selects "Field1" from the filter
drop-down, I only want to display records in the dataset that have a value in
Field1 (i.e. not show those with null values). The same would hold true if
the
user would select "Field2". If the user does not select a value for this
parameter (i.e. it is blank/null), I want to show all records.
"Carl Henthorn" wrote:
> An example of your code would be helpful. when you say "Filter", are talkign
> about reducing the size of your result set by using the defined parameters in
> a where clause. it looks like you want to return different columns from a
> table depending on which parameters are picked. Which is it? both?
> if you are talking columns, use the visibility expression to hide the
> unwanted columns based on the parameter value. if you are talking where
> clause, then you can get around using dynamic sql by using a case stmt in the
> stmt. I.e. where field1=case when @.param1<>'' then @.Param1 else field1 end
> hth!
> "David" wrote:
> > Embedded sql...
> >
> > "Carl Henthorn" wrote:
> >
> > > are you using embedded sql code, or a stored procedure call?
> > >
> > > "David" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a Reporting Services report that I would like to add a dynmaic filter
> > > > to. I want to allow the users to filter based on two seperate fields (or
> > > > view all data). Example: if the user selects "Field1" from the filter
> > > > drop-down, I only want to display records in the dataset that have a value in
> > > > Field1 (i.e. ignore those with null values). The same would hold true if the
> > > > user would select "Field2". If the user does not select a value for this
> > > > parameter (i.e. it is blank), I don't want to limit the data.
> > > >
> > > > Is this possible? I would prefer to accomplish this without using dynamic
> > > > sql.
> > > >
> > > > Let me know if you have any questions...
> > > >
> > > > Thanks for the help!
> > > >
> > > > -David.

Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

Follow

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani