Ecommerce Database Design

First, is there a forum here for advanced database design that I'm missing?

I have an ecommerce project I'm working on for which I have to develop a database that will house many products under many categories with unlimited subcategories/levels. For example:

baseball
baseball \ weightlifting
baseball \ weightlifting \ upper body
baseball \ weightlifting \ lower body \ ...

The levels of subcategories must be completely scalable, as the administrator wants to be able to add and subtract at will. Further, there is the idea that some subcategories will transfer across other categories (ex: weightlifting would be a subcategory of multiple sports).

In previous database designs I'vesimply have a category table and maybe a subcategory table; obviously that won't work here, as the number of levels of categories is unknown.

Does anybody have any suggestions, examples, recommended books, forums, etc. to point me in the right direction?

Any help would be greatly appreciated.

BrianOk, Brian... I have designed simmilar type of the databases... the most important thing U should remember is that U deal with relational database instead of treewise... so U must represent the data as a couple of relations (in other words, tables) and link them in Ur queries.
So... for example... place all the categories U have into the table Categories (CatID, Name) and create the table... Relations (RelID, CatID, ParentCatID). So... the Categories should contain the data:

CatID | Name
____________
1 | baseball
2 | weightlifting
3 | upper body
4 | lower body

And the Relations would be:

RelID | CatID | ParentCatID
________________________
1 | 1 | 0 -- that means: "no parent category".
2 | 2 | 1
3 | 3 | 2
4 | 4 | 2

and so on...|||Thanks very much for the suggestion.

I was thinking about having the parentID in the category table. For example:

CatID | ParentID | CatName
-----------
1 0 Baseball
2 1 Weightlifting

...

Is there some advantage to having a separate table for the relations that I'm missing?|||oh... U can for shure ... even to say there is redundant data in my case... yes. If wonna discuss anything more... in more rapid mode... here u r : ICQ# 303978464.

Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

Easy Question: Handling Login Exceptions

I have some very simple code that connects to an SQL Server instance using the SMO Server object. I have everything working fine, but I would like to be able to know if the login failed and respond to the error. Does anyone have a snippet that will do this? Do have to catch an exception or handle an event or....

Thanks!

This is how I connect in SMO (VB.Net):

' Connect to the server

Dim srvMgmtServer As Server

srvMgmtServer = New Server("MyServer\MyInstance")

Dim srvConn As ServerConnection

srvConn = srvMgmtServer.ConnectionContext

srvConn.LoginSecure = True

Hope that helps.

|||Ok, that is fine, but what if "MyServer\MyInstance" doesn't exist, or the user supplies invalid credentials? I want to be able to tell the user the login failed and why. There must be an exception thrown somewhere, but I can’t find a simple way to handle an invalid login. Right now I am opening the connection to the server right after I create the instance, and then catching any exceptions, but this seems like a bit of a hack. There has to be a way to do something like this:

try
{

srvMgmtServer = New Server("MyServer\MyInstance")

}
catch(SomeException)
{

//Do Stuff Because The Login Failed

}
|||

Yes, I'm sorry. The method of handling the exception is exactly what you've shown. If you catch(ex), then you can scroll through the InnerException property until you get to the base level. When I just catch the error at the top level the Message property contains ""Failed to connect to server MyServer\MyInstance."

In VB.Net the code looks something like this:

Catch ex As Exception
Console.WriteLine("There has been a VB error. " + ex.Message)
Do While ex.InnerException IsNot (Nothing)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
End Try

Hope that helps.

sql

easy (hopefully) matrix question

I'm working with a matrix in the designer and noticed I need to add a new row in between two existing rows. When I right click and choose add row a row is added at the very bottom of the matrix. What's the easiest way to add a matrix row to the middle of a matrix, one that doesn't include editing the rdl directly.

thanks

I had this exact problem!

What I found out is that if you carefully (yes, carefully) drag your field between 2 existing data fields, there's a thin white bracket line that shows up on the outside of the nearest existing textbox. This is to alert you where the field will be placed. It will either be on the left, right, top or bottom. If none of these areas are white then keep looking because otherwise you'll replace the destination cells - (bad).

|||Thanks for the response! I can insert a row below the first row but every other row is missing that white line that indicates I can insert.

Can you insert between every row or just between the first and second rows?|||You should be able to do it anywhere.. try moving the mouse around more.
The only time I see that you don't get the white bar is when you don't even have an existing data field in the nearest textbox.|||
You're right. After expanding the box and some very careful mouse work I got it to insert. Hopefully the MSFT guys will make this very basic function easier to use. Thanks again for the help.

Dynamically formatting data using XSD

Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
Sameer
Hello Sameer,
I think you might be getting confused as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent
|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting confused as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concept
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directly
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent

Dynamically formatting data using XSD

Hi,
I am working on following requirement:
Data stored in a table (SQL Server 2005 database) needs to be retrieved
into a .Net application. The columns that need to be retrieved (the
schema) will be predefined by users. Data needs to be retrieved for
each user based on the schema defined by the user (The schema will
define columns and the order of the columns).
I am analyzing different options like storing the schema in database
(XML Schema collection) or in a physical file (XSD) and retrieving them
using XQuery. However, as the data is not stored as a XML data type,
the options are not working.
Any guidance, suggestions would be appreciated.
Thanks.
Regards,
SameerHello Sameer,
I think you might be getting as to what a XSD Schema is, what an
XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
an XSD schema is an XML document that can be used to describe and validate
another XML instance. An XML Schema Collection is a SQL Server 2005 concept
for storing the schemas that describe an XML instance. It consists of one
or XSD Schemas, usually on a per namespace basis. Neither of these directly
provide that you're looking for.
It sounds like what you are looking for is Annotated XSD Schemas. BOL does
a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
4.0"
Thanks,
Kent|||Thanks Kent.
Kent Tegels wrote:
> Hello Sameer,
> I think you might be getting as to what a XSD Schema is, what an
> XML Schema Collection is and Annotated XSD Schemas. It boils down to this:
> an XSD schema is an XML document that can be used to describe and validate
> another XML instance. An XML Schema Collection is a SQL Server 2005 concep
t
> for storing the schemas that describe an XML instance. It consists of one
> or XSD Schemas, usually on a per namespace basis. Neither of these directl
y
> provide that you're looking for.
> It sounds like what you are looking for is Annotated XSD Schemas. BOL does
> a good job of covering these in the topic "Annotated XSD Schemas in SQLXML
> 4.0"
> Thanks,
> Kent

Dynamically determining the SQL statement?

Hello all,

I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.

Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.

With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).

Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).

BTW...currently using SSRS 2005 + ASP.NET 2.0.

Thanks for your help!!

-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>

Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...

I guess the *best* way to set the command text and datasource from the front end is the use parameters?

Are there any other possible solutions?

Thanks!

-Brian

Dynamically creating SQL Server Report

I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>

Holds.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;
// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
 {
if (!IsPostBack)
 {
 Session["Season"] =null;
 }
if (Session["Season"] !=null)
 {
 ConfigureCrystalReports();
 }
 }
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
 {
 myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
 myReport.Load(reportPath);
 DataSet dataSet = DataSetConfiguration.Holds;
 myReport.SetDataSource(dataSet);
 CrystalReportViewer1.ReportSource = myReport;
 }
// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
 {
if (ddlSeason.SelectedIndex == 0)
 {
 Session["Season"] =null;
 }
else
 {
 Session["Season"] = ddlSeason.SelectedValue;
 ConfigureCrystalReports();
 }
 }
}

DataSetConfiguration.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
 {
//
 // TODO: Add constructor logic here
 // }// Handles creating holds datasetpublic static DataSet Holds
 {
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;
 DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";
 dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
 SqlDataAdapter cmd =new SqlDataAdapter(query, conn);
 cmd.Fill(dataSet,"Holds");
return dataSet;
 }
 }
}

Thanks!

I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>

Holds.aspx.cs

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } }

DataSetConfiguration.cs is the same.

FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp

Dynamically changing SQL ConnectionString for live/test environments

Hello. What I'm trying to do should be fairly simple, but after an hour of searching and trials, I can't seem to get it working.

When I run my website on my test machine, I would like it to use a certain value for my connectionString. But when it's run on the live server, it should use a different connection string. This way, when it's on the test server it'll use the test database.

In my Web.Config file, I have this:

<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=TEST_DB;Initial Catalog=tester;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I would like to be able to change the connectionString in something like the Global.asax file, when the application is loaded. I'd check for a certain environment variable, or some other condition, and change the value of the connectionString.

I tried this in my Global.asax, but it told me the value was read_only:

ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString = "Data Source=LIVE_DB;Initial Catalog=live;Integrated Security=True"

Does anyone have a good way of doing this, so I don't need to juggle Web.Config files when I publish my site?

Thanks.

You can have a separate app.config file for production or have a separate configuration section so that when you build the application (deploy), Visual Studio will replace the connection strings

Take a look at this post (http://msdn2.microsoft.com/en-us/library/bb164473(VS.80).aspx)

|||

After a more varied search, I came across the Web Deployment Projects tools.

http://msdn2.microsoft.com/en-us/asp.net/Aa336619.aspx

Now I've got it simply including a different piece of config code when it builds the website in either Debug or Release mode.

Thanks for the reply, though. I'm all set now.

Dynamically change the DataFlow Queries

Hi Guys,

This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.

Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.

Can anyone help me in this..

Regards,
Ravi K. Kalyan
Mascon Global Limited.

You can only change the query if the metadata of the data-flow is unchanged thereafter.

If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

It tells you how to dynamically alter your SQL queries.

-Jamie

|||

Thanks for the reply.

I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.

I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.

I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.

--Kalyan

dynamic table name (was "Please help ! SQL")

Hi--

I am new to T-SQL i got this code from some website but its not working can anyone let me know why
@.cur_tab_name varchar(30) is not decleared while i have decleared
I want to use this for dynamic name of the table.

Thanks.


create table temp_tab
(
tab_name varchar(30),
no_of_rows INTEGER,
)

DECLARE
curREVIEW

CURSOR FOR
select name
from sysobjects
where xtype = 'U'

DECLARE @.cur_tab_name varchar(30)

OPEN curREVIEW

FETCH curREVIEW INTO @.cur_tab_name

WHILE (@.@.FETCH_STATUS =0)
BEGIN
DECLARE @.count integer

select @.count = count(*) from @.cur_tab_name
INSERT INTO temp_tab
(@.cur_tab_name, @.count)

FETCH curREVIEW INTO @.cur_tab_name

END

CLOSE curREVIEW

DEALLOCATE curREVIEWDynamic SQL must be passed as a string to EXEC:

EXEC ('select @.count = count(*) from ' + @.cur_tab_name)

...but this still will not give you what you want, because the code will EXECute in a different scope than your @.count variable.

Try this:

Declare @.SQLString varchar(500)
Set @.SQLString = 'INSERT INTO temp_tab select ''' + @.cur_tab_name + ''', count(*) from ' + @.cur_tab_name
EXEC (@.SQLString)

Dynamic SQL Query

I am having trouble getting this dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.
I want to build this query with only values that have values that
are passed, if they are null, no need to be included in query. This
almost seems to work
except I have issues with getting the query to work when the field is
an int.
I get this error
Syntax error converting the varchar value 'select * from company where
categoryid = '' to a column of data type int.
Assistance would be appreciated.
_________________________________
CREATE PROCEDURE stp_Search
@.categoryid int = NULL,
@.commodityid int = NULL,
@.companyname varchar(25) = NULL,
@.streetname varchar(25) = NULL,
@.communityid int = NULL,
@.status varchar(1) = NULL
AS
DECLARE @.sql varchar(4000)
SELECT @.sql = 'select * from company '
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
''''
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
EXEc(@.sql)pisquem@.hotmail.com,
Are you sure you want to concatenate the logical expressions below, based on
@.categoryid?

> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
They way you are doing it is not a good practice. Try using parameters with
sp_executesql and you will not have to worry about casting, sql injection,
etc.
SELECT @.sql = 'select c1, c2, ..., cn from dbo.company where 1 = 1'
IF @.categoryid IS NOT NULL
SELECT @.sql = @.sql + ' and categoryid = @.categoryid'
IF @.commodityid IS NOT NULL
SELECT @.sql = @.sql + ' and commodityid = @.commodityid'
IF communityid IS NOT NULL
SELECT @.sql = @.sql + ' where communityid = @.communityid'
IF @.companyname IS NOT NULL
SELECT @.sql = @.sql + ' and companyname = @.companyname'
IF @.status IS NOT NULL
SELECT @.sql = @.sql + ' and status = @.status'
exec sp_executesql @.sql, N'@.categoryid int, @.commodityid int, @.companyname
varchar(25), @.streetname varchar(25), @.communityid int, @.status varchar(1)',
@.categoryid, @.commodityid, @.communityid, @.companyname, @.status
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"pisquem@.hotmail.com" wrote:

> I am having trouble getting this dynamic query in a sp working and the
> code is below so can someone please help me? It would be very much
> appreciated.
> I want to build this query with only values that have values that
> are passed, if they are null, no need to be included in query. This
> almost seems to work
> except I have issues with getting the query to work when the field is
> an int.
> I get this error
> Syntax error converting the varchar value 'select * from company where
> categoryid = '' to a column of data type int.
>
> Assistance would be appreciated.
> _________________________________
> CREATE PROCEDURE stp_Search
> @.categoryid int = NULL,
> @.commodityid int = NULL,
> @.companyname varchar(25) = NULL,
> @.streetname varchar(25) = NULL,
> @.communityid int = NULL,
> @.status varchar(1) = NULL
> AS
> DECLARE @.sql varchar(4000)
> SELECT @.sql = 'select * from company '
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where categoryid = ''' + @.categoryid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where commodityid = ''' + @.commodityid + ''''
> IF @.categoryid IS NOT NULL
> SELECT @.sql = @.sql + ' where communityid = ''' + @.communityid + ''''
> IF @.companyname IS NOT NULL
> SELECT @.sql = @.sql + ' and companyname LIKE ''' + @.companyname +
> ''''
> IF @.status IS NOT NULL
> SELECT @.sql = @.sql + ' AND status LIKE ''' + @.status + ''''
> EXEc(@.sql)
>

Dynamic SQL Newbie needs urgent help

Have a report that is working fine. Customer wants to add another field to
it. I have a field name of navigationPK to get the field they want. Only
problem is is that the navigationPK can be a primary key from any number of
tables.
Scenario......
Have a usertask displayed on dashboard....need to do a report on all
overdue tasks. Each task has a particular navigationpk. This navpk can be
linked to three or four tables. ie....navpk 1189134 could be the pk in a
tpp table, rvc table or a tr table. Need to ascertain which one it is so
that I can get the relevant additional field from each table.
Kinda hard to explain. But if anybody wants anymore info then please ask.
I was told to do dynamic sql...only prob is I have no idea where to start
and how to fit into existing script. Below is my initial script....works
fine so far. Needs tidying up I know, but essentially it works.
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
--INNER JOIN personherdrole phr on p.id = phr.personid
--INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
The additional field required is the herdid, and this herdid could be from
the tpp table, rvc table, tr table .
Help please. Going round in circles and not sure where to start.Article about dynamic sql
http://www.sommarskog.se/dynamic_sql.html
You did not describe the rules as to how you know which table it will be in.
Can it be in multiple tables but you only want a specific one?
Or the HerdID will only be in one of the tables?
If so
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
INNER JOIN personherdrole phr on p.id = phr.personid
INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
Inner JOIN (SELECT newfield, HerdID
FROM tpp
UNION
SELECT newfield, HerdID
FROM rvc
UNION
SELECT newfield, HerdID
FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
No Dynamic required
"Nat Johnson" wrote:
> Have a report that is working fine. Customer wants to add another field to
> it. I have a field name of navigationPK to get the field they want. Only
> problem is is that the navigationPK can be a primary key from any number of
> tables.
> Scenario......
> Have a usertask displayed on dashboard....need to do a report on all
> overdue tasks. Each task has a particular navigationpk. This navpk can be
> linked to three or four tables. ie....navpk 1189134 could be the pk in a
> tpp table, rvc table or a tr table. Need to ascertain which one it is so
> that I can get the relevant additional field from each table.
> Kinda hard to explain. But if anybody wants anymore info then please ask.
> I was told to do dynamic sql...only prob is I have no idea where to start
> and how to fit into existing script. Below is my initial script....works
> fine so far. Needs tidying up I know, but essentially it works.
> select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
> ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
> ut.DueDate As DueDate
> --h.id as HerdId
>
> from person p
> --INNER JOIN personherdrole phr on p.id = phr.personid
> --INNER JOIN herd h on phr.Herdid = h.id
> INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
> INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
> INNER JOIN usertask ut on ut.UserName = p.UserName
> where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>
> The additional field required is the herdid, and this herdid could be from
> the tpp table, rvc table, tr table .
> Help please. Going round in circles and not sure where to start.
>|||1. create your select statement
2 run the select statement and generate fields
3. generate report
last but not least turn the sql into a string after you have everything
working. us a report parameter to pass the dynamic part to the querry.
like this
="select * from " + Parameters!company.value
its that easy.
rmartinez wrote:
>Article about dynamic sql
>http://www.sommarskog.se/dynamic_sql.html
>You did not describe the rules as to how you know which table it will be in.
>Can it be in multiple tables but you only want a specific one?
>Or the HerdID will only be in one of the tables?
>If so
>select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
>ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
>ut.DueDate As DueDate
>--h.id as HerdId
>from person p
>INNER JOIN personherdrole phr on p.id = phr.personid
>INNER JOIN herd h on phr.Herdid = h.id
>INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
>INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
>INNER JOIN usertask ut on ut.UserName = p.UserName
>Inner JOIN (SELECT newfield, HerdID
> FROM tpp
> UNION
> SELECT newfield, HerdID
> FROM rvc
> UNION
> SELECT newfield, HerdID
> FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
>where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>No Dynamic required
>> Have a report that is working fine. Customer wants to add another field to
>> it. I have a field name of navigationPK to get the field they want. Only
>[quoted text clipped - 31 lines]
>> Help please. Going round in circles and not sure where to start.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Dynamic SQL Issues with Functions/SP

I am working on a reporting application that uses dynamic SQL to generate
result sets. My client initially used a non-dynamic user-defined function
that returned a table, but when I made the SQL dynamic, it turns out that
EXEC statements cannot be used within a function. Is there a way to return a
table variable from a stored procedure if I convert the function into a
stored procedure? Are there any workarounds for this?
--
Larry Menzin
American Techsystems Corp.>> Is there a way to return a table variable from a stored procedure if I
Table variables cannot be used as parameters or return values, however you
can use regular resultsets for similar functionality. Also see some other
alternatives at:
http://www.sommarskog.se/share_data.html
Anith|||Why are you trying to make udf dynamic? Why not, in your dynamic SQL, simply
include a call to the function?
Thomas
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||You can return a result set from a Stored Procedure with a SELECT statement.
Here's a really simplified example that uses dynamic SQL:
CREATE PROCEDURE dbo.usp_test
AS
DECLARE @.MySQL AS NVARCHAR(4000)
SET @.MySQL = N'SELECT * FROM master.dbo.sysmessages'
EXEC dbo.sp_executesql @.MySQL
GO
EXEC dbo.usp_test
GO
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
> a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
No
Can you explain what are you trying to accomplish?
AMB
"Larry Menzin" wrote:

> I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||I am trying to join 4 result sets together, where each set is currently
generated as a table output variable from a UDF. UDF output is used directly
in JOIN statements in a stored procedure. If I convert UDFs to stored
procedures can I use stored procedure result sets in joins like UDF table
variables are used in joins, i.e.,
Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
ExceSP1(Parameters3) ON ...
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> No
> Can you explain what are you trying to accomplish?
>
> AMB
> "Larry Menzin" wrote:
>|||You can use VIEWs in the manner you're talking about. Another option is to
create Temporary Tables with SELECT INTO or INSERT INTO in your SP and JOIN
them.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0E155215-A119-4980-B1B9-7DFC110E22AF@.microsoft.com...
>I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used
> directly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||Larry,
No, you can not reference stored procedure from the "from" clause, but as
Anith pointed, there are other alternatives, like capturing the sp resultset
into a table (temporary or permanent) and use that table in subsequent
statements. Click the link posted by Anith Sen.
AMB
"Larry Menzin" wrote:
> I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used direct
ly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||> If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
No and I don't see a reason why you would need to do that. You should be abl
e to
do the following:
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
...
This assumes that all of the functions are table-valued functions. If there
is
commonality amongst the parameters in the functions, you could create a sing
le
stored procedure that returns the data like so:
Create Procedure ReportXYZ(parameter1 as ...)
As
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
Return
Notice that no dynamic SQL is needed for any of this. You would simply call
the
stored procedure which would return the raw data for you.
Thomas|||Here's one of I'm sure many articles on this subject:
http://www.databasejournal.com/feat...cle.php/3386661
jp
Larry Menzin wrote:
> Alejandro,
> Can I capture the result set from the SP into a table variable, i.e.,
> DECLARE @.CurrentPeriodDataPrelim TABLE(
> Product VarChar(40)
> ,Region varchar(40)
> ,Market varchar(40)
> ,Channel varchar(40)
> ,MRC float
> ,Net float
> ,NumerLabel nVarChar(150)
> )
>
> Declare @.strSQL nvarchar(2000)
> Set @.strSQL = N'
> INSERT INTO @.CurrentPeriodDataPrelim (Product, Region, Market, Channel, MR
C,
> NET, NumerLabel)
> SELECT cat as Product, ...
> Or do I have to use temp tables with the '#' designation if I don't want t
o
> use permanent tables. Also, don't I worry about temp tables going out of
> scope?
>
>

Dynamic sql in dataset not working

I'm trying to have the sql in my dataset dynamic based on report parameters. I expected this to work, but doesn't. Dataset:

="select item from table where " + Parameters!SearchType.Value + " = " + Parameters!Filter.Value + "order by item"

Is this possible or do I need to take a different approach (and what would that approach be)

Thanks,

Derek

Try the expression in a report textbox first and look at the generated query commandtext in preview to see if it is correct.

From the expression you posted it looks like you generate invalid syntax for the query string because there seems a missing blank before "order ..."

-- Robert

|||

Hi Robert,

It is possible but make sure thet you have the filter value inside a single quote (') so that SearchType of any datatype will work. so your expression should look like this:

="select item from table where " + Parameters!SearchType.Value + " = '" + Parameters!Filter.Value + "' order by item"

(also note the blank space before order by clause)

Shyam

|||

I've been able to get this working for a dataset accessing sql server, but i'm having trouble getting it to function with an oracle datasource. Should this work for Oracle or do I need to head down a different road - maybe plsql for this?

Thanks!