Building Dynamic SQL In Stored Procs
Oct 24, 2000
We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?
' This is some VB code that shows how the query differs based on a parameter.
If Me![Sorting] = 1 Then
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table] __
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;"
Else
Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__
[DVD_Projects_Table].Date FROM [DVD_Projects_Table]__
WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __
(([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __
[DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));"
End If
This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.
CREATE PROCEDURE [procDVDProjectsList]
@SortBy as bit,
@ActiveOnly as bit,
@SysFilter as integer
AS
SELECT
CASE @SortBy
/* Display Title first */
WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date]
/* Display Number first */
WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date]
END
FROM
[DVD_Projects_Table]
WHERE
CASE @SysFilter
/* List all */
WHEN 0 THEN
/* List only NTSC */
WHEN 1 THEN [DVD_Projects_Table].[System] = "525"
/* List only PAL */
WHEN 2 THEN [DVD_Projects_Table].[System] = "625"
END
AND
CASE @ActiveOnly
/* List All */
WHEN 0 THEN
/* List only active */
WHEN 1 THEN [DVD_Projects_Table].Active = True
END
ORDER BY
CASE @Sortby
/* Sort Alpha */
WHEN 0 THEN [DVD_Projects_Table].Title
/* Sort Numeric */
WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4)
END
Thanks for your help,
-Sumit Malik
View 1 Replies
ADVERTISEMENT
Feb 4, 2008
I ran across this technique being used in an application the other day. It seems not a good idea to me. What do you think?
1. The proc builds a basic query, nothing real fancy, into a string variable called @SQL defined as varchar 2000. Depending on the result desired, the group by clause can be one of three different sort orders.
2. The string is executed via EXEC @SQL.
It seems to me that the whole process can eliminate the EXEC and just use some other construct. All the parameters are passed in via the initial call to the stored proc. It also seems that every time this is executed it will result in a new query compile and cache useage, no matter what. Wasteful? Should I take the developers aside and knock heads? I think the app was coded by some folks who were rookies then but may be willing to crack open their code. Or, am I the one that is a rookie?
Thanks for your inputs.
View 11 Replies
View Related
Mar 30, 2005
Hi
I am in the very final stages of
building a dating app for a client, I am totally stuck with the
advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page;
county (Dropdown list)
min age (Dropdown list)
max age (dropdown list)
Smoker (check box)
keyword (textbox)
My codebehind passes the vars to a stored procedure
@county = me.county.selectedvalue
etc
My problem is the stored procedure I sort of have the following but I can't get it to run
<code>
ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH
(@countyID int ,
@MaxAge
varchar(100),
@MinAge
varchar(100),
@smoker tinyint),
@keyword
varchar(250))
AS
DECLARE @SQL Varchar
(4000)
SELECT @SQL = 'dbo.user_accounts.profileComplete,
dbo.user_accounts.countyID, dbo.user_profiles.smoker,
dbo.user_profiles.Age
FROM dbo.user_accounts INNER
JOIN
dbo.user_profiles ON dbo.user_accounts.userID =
dbo.user_profiles.userID
WHERE (dbo.user_accounts.profileComplete =
1)'
IF @countyID > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_accounts.countyID = @countyID)'
IF @MaxAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age <= @MaxAge)
'
IF @MinAge IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Age >= @MinAge)
'
IF @smoker > 0
SELECT @SQL = @SQL + ' AND
(dbo.user_profiles.smoker = 1)'
IF @keyword IS NOT
NULL
SELECT @SQL = @SQL + ' AND (dbo.user_profiles.Description LIKE @MinAge)
'
EXEC(@SQL)
</code>
If I can get this to work I can add the remaining fields that I need
Am I Missing something glaringly obvious?
Any help or advice gratefully received
Thanks
View 3 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble;
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)AS
declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 10 Replies
View Related
Apr 19, 2008
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;
Code:
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
)
AS
declare @StaticStr nvarchar(5000)
set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userID
FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID '
if(@regionID <> 0)
set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
exec sp_executesql @StaticStr
)
View 2 Replies
View Related
Oct 14, 2004
Hi all,
I'm gonna need some help with this one.
I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:
SELECTN_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROMdbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ...............and so on
What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.
So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!
Thanks
View 2 Replies
View Related
Dec 5, 2007
Hi,
I have read in many articles that Dynamic SQL should not be used in Stored Procedures as it is not a Best Practice. So Can anyone tell me how to handle the following scenario without using Dynamic SQL in Procs. The scenario is as follows
I have to write a Procedure which will generate a Report from a Table. For this purpose I have to Select about 20 columns from a Table (lets say Table A). Now the report has 5 filters which are completely optional. I mean the user might fill few of the filters or all the filters or none of the Filters
So can anyone help me out in this scenario because my "where" clause is completely Dynamic and I am unable to understand how to handle my where clause without writing Dynamic Queries. Also I am trying to write this query on SQL Server 2000.
If anyone can give me some examples or articles which can be of help it would be great. Please help
Regards
Vineed
View 6 Replies
View Related
Mar 3, 2006
Hey all,
So here's my deal, I've got a bunch of tables set up like so.
CaseName_Class
CaseName_Address
CaseName_Mailing
The casename obviously changes based on the name of the case, but all table share the "_Class", "_Address" etc.
Now, I have built apps for these to do various things, handling mail, logging claims in, whatever. And at this point in time each case has a new directory with the files inside that directory that all do the same thing. Reason why this sucks is that if I change an app in one place, I should be hanging it in about 100 other places which causes...
So, what I am attempting to do is created a stored proc in SQL to handle one of the applications, pass in a few variables and make all of the cases run off of that one proc...yay! go me.
Now...due to the fact I have to pull the case prefix name everytime the proc runs, I am kind of shooting myself in the foot being that the proc has to recompile everytime to get that casename.
The code that is currently there and works, looks like this
Code:
SET @dynSQLBox = N'SELECT @BoxCount1 = COUNT(MailingID) ' + char(10) +
'FROM ' + @Client + '_Mailing' + char(10) +
'WHERE MailEventID = ' + CAST( @MailEventID as varchar(20) ) + char(10) +
'AND MailTypeID = ' + CAST( @MailTypeID as varchar(20) ) + char(10) +
'AND box = ' + @Box
Just as an example that is looking for the max box count inside of a box inside of the specific case.
I am wondering if there is a better way to go about getting data that has to be dynamic inside of a stored proc.
I hope this makes sense, and look forward to talking about it.
Thanks!
Caden
View 1 Replies
View Related
Jul 6, 2001
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
View 12 Replies
View Related
Jul 6, 2001
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
View 2 Replies
View Related
Jul 6, 2001
Hello,
Does anyone have any experience or knowledge of being able to send an
unlimited amount of parameters to a stored proc and having the stored proc
detect how many parameters were sent?
Example:
1) calling the stored proc with 2 strings:
exec sp_myproc "a","b"
2) calling the stored proc with 7 strings:
exec sp_myproc "a","b","c","d","e","f","g"
The following sp_myproc would need to be able to support an unlimited amount
of parameters as well as detect how many were sent...
Thanks in advance for any help and/or code samples given!
-- Al
View 4 Replies
View Related
Jan 15, 2008
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
View 1 Replies
View Related
Jan 17, 2008
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need.
I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values.
Here's an example "core" query:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode
From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC')
My code would add the last line above since the user specified that they only wanted NC profiles.
This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID
From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like:
SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John'
You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me.
The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it.
If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible.
This app is using .NET 2.0 and written in VB.NET. Thanks for any help!
View 14 Replies
View Related
Jul 20, 2005
Hi;I would like to read a list of tables from a temp table and then do asql statement on each table name retrieved in a loop, ie:-- snip cursor loop where cursor contains a list of tablesdeclare @rec_count intset @rec_count = 0exec('select @rec_count = count(myfield) from ' + @retrievedTableName)This does not work. SQLSERVER tells me @rec_count is not declared.How can I get the @rec_count populated....or can I?Thanks in advanceSteve
View 3 Replies
View Related
May 7, 2004
HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
View 4 Replies
View Related
Feb 18, 2008
Thanks in advance for taking the tiemt o read this post:
I am workingon an application in vb.net 2008 and I have 5 drop down lists on my page.
I have code that worked in .net 2005 for my databind but would like to use new features in 08 to do this same thing.
Here is my 05 code how would I do this same things in 08?
Dim db As New DataIDataContext
Dim GlobalSQLstr As String
GlobalSQLstr = "select Orig_City, ecckt, typeflag, StrippedEcckt, CleanEcckt, ManualEcckt, Switch, Vendor, FP_ID, order_class, Line_type, id from goode2 where 1=1"
If (ddlOrigCity.SelectedValue <> "") Then
GlobalSQLstr &= "and Orig_City = '" & ddlOrigCity.SelectedValue & "'"
End If
If (ddlSwitch.SelectedValue <> "") Then
GlobalSQLstr &= "and switch = '" & ddlSwitch.SelectedValue & "'"
End If
If (ddlType.SelectedValue <> "") Then
GlobalSQLstr &= "and Order_Class = '" & ddlType.SelectedValue & "'"
End If
If (ddlFormatType.SelectedValue <> "9") Then
GlobalSQLstr &= "and typeflag = '" & ddlFormatType.SelectedValue & "'"
End If
If (ddlVendor.SelectedValue <> "") Then
GlobalSQLstr &= "and Vendor = '" & ddlVendor.SelectedValue & "'"
End IfDim AllSearch = From A In db.GoodEcckts2s
If (ddlErrorType.SelectedValue <> "0") Then
GlobalSQLstr &= "and ErrorType = '" & ddlErrorType.SelectedValue & "'"
End IfDim cmd As New SqlClient.SqlCommand
Dim rdr As SqlClient.SqlDataReaderWith cmd.Connection = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)
.CommandType = Data.CommandType.Text
.CommandText = GlobalSQLstr
.Connection.Open()
rdr = .ExecuteReaderMe.gvResults.DataSource = rdrMe.gvResults.DataBind()
.Connection.Close()
.Dispose()End With
View 4 Replies
View Related
Nov 4, 2015
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)
I get the following error: Invalid object name 'inserted'
I know I can do:
Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)
But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?
View 2 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
May 12, 2008
Hi All,
What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign.
I've tried various ways to create this query and it always truncated around the point of the % sign.
Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings!
Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc.
Thanks for any help on this!
regrads
davej
@var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE'
@var3 = ''%london%' OR item_Text LIKE '%solicitor%''
@var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
@varfull = @var1+@var2+@var3+@var4
View 11 Replies
View Related
Oct 8, 1999
I'm fairly new to MSSQL 7.0, and am having a problem. I would like to create a Stored Procedure that builds a Union of the result sets from two Stored Procedures. In reality, it is two calls to a single stored procedure, with different parameters.
I can't find any documented method to actually perform any operations on the result set of a stored procedure. Is there any way to get a SELECT statement to return the results of a Stored Procedure call?
Thanks in advance!
Stephen Cochran
View 2 Replies
View Related
Feb 5, 2005
almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --
Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)
i don't think i can do justice to how utterly stupid that stored procedure is
read the comments and have a laugh
one of the points made was that stored procedures are bad
this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --
Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
enjoy!
View 6 Replies
View Related
Jun 28, 2007
Hey guys.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Thanks
tibor
View 14 Replies
View Related
Apr 8, 2004
First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND
GO
CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)
RETURNS float
AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO
CREATE PROCEDURE usp_shapes_GetRectangularXSection
@fResult float OUTPUT
AS
declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10
SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere
GO
CREATE PROCEDURE usp_shapes_GetXSection
@fResult float OUTPUT
AS
EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO
declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO
DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO
Mike B
View 3 Replies
View Related
Nov 13, 2007
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
View 5 Replies
View Related
Aug 8, 2006
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
View 10 Replies
View Related
Feb 27, 2008
This should be an easy enough answer to find if I just knew what to search on!
I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says:
select * from tblNames where FirstName = 'Hannah'
or
select * from tblNames where LastName = 'Montana'
or
select * from tblNames where FirstName = 'Hannah' and LastName='Montana'
My problem is putting the single quotes around the variable value.
SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it
Thanks
View 3 Replies
View Related
Feb 8, 2008
Hello All,
I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria.
WhereClause from UI: whereClause="where DefectImpact='High'"
SQL Query in SP: SELECT @sql='select * from tablename'
Exec(@sql + @whereClause )
Here i am not able to get the results based on the search criteria. Instead i am getting all the results.
Please help me in this regard.
Thanks,
Subba Rao.
View 11 Replies
View Related
Mar 8, 2004
All,
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
View 3 Replies
View Related
Apr 12, 2004
Hi all!
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS
SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb
FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0
ORDER BY
ProductName,
VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
Suggestions?
Any input is highly appreciated.
Thanks in advance,
Mili Skikic
View 8 Replies
View Related
Mar 25, 2002
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
this is sql7, sp3, development box.
thanks,
View 1 Replies
View Related
Apr 27, 2001
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store
Any help or suggestions would be greatly appreciated!
trevorb
View 1 Replies
View Related
Jun 19, 2000
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate
View 2 Replies
View Related
Sep 10, 2001
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
View 6 Replies
View Related
