Stored Procedure Dynamically Determine Columnname

Dec 3, 2006

I want to dynamically determine which column I select data from. The table I want to select from looks like this:

tblSexuality
SexualityID int PK
EN nvarchar(20)
NL nvarchar(20)
DE nvarchar(20)

Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"
In the sample below the value of @LanguageColumnName is "NL".
Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?

ALTER PROCEDURE [dbo].[spFindUsers]
@LanguageColumnName nvarchar(3),
@startRowIndex int,
@maximumRows int
AS
BEGIN
SET NOCOUNT ON;

declare @tblSexualityName nvarchar(40)
set @tblSexualityName='tblSexuality.' + @LanguageColumnName
SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,
IsMale,NearestBigCity,DistanceToNearestBigCity,Description,
IsDonator,IsVIP,SexualityName
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,
tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,
tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users
INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID
INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID
INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID
) as MemberInfo
WHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)
END





 

View 1 Replies


ADVERTISEMENT

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Dynamically Execute Stored Procedure

Oct 31, 2005

Hello,I was wondering if it is possible to dynamically execute a stored procedure; for example, in SQL, you can do:insert into Table1(   id, name)select id, namefrom Table2Can you do something like:exec spProc @id = id, @name = namefrom Table1Or something like that?  I know I can select a row at a time and execute, or write a program, but I was looking to see if there was an easier way.Thanks.

View 1 Replies View Related

Dynamically Determine Linked Servers

Oct 26, 2007

In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done?




CREATE FUNCTION dbo.stfGetSupervisorOrgUnitAssignments(@SupervisorID INT)

RETURNS @ReturnTable TABLE(Org_Unit INT PRIMARY KEY)

AS

BEGIN

DECLARE @Server VARCHAR(100)

SELECT @Server = dbo.stfGetLinkedServerName()


INSERT INTO @ReturnTable(Org_Unit)

SELECT ReplicationID FROM OPENQUERY(@Server, '....')



END

Any help would be greatly appreciated.

View 6 Replies View Related

How To Dynamically Create SQL Inside A Stored Procedure?

Feb 28, 2005

I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?



ALTER PROCEDURE dbo.spGetNextPageRecords

(
@pageSize int,
@previousMaxId int

)

AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN

View 4 Replies View Related

Stored Procedure Parameters Update Dynamically

Sep 22, 2014

I set up a stored procedure. My stored procedure has 8 variables declared at the beginning like this:

===========================
declare@IncurredDateStartsmalldatetime
,@IncurredDateEndsmalldatetime
,@PaidDateStart_CUsmalldatetime
,@PaidDateEnd_CUsmalldatetime
,@PaidDateStartsmalldatetime
,@PaidDateEndsmalldatetime

[Code] ....

All of these dates are based on the update of our data warehouse. This stored procedure runs a 5 step process and produces data for 8 - 10 monthly reports.

I was wondering if these variables can be updated dynamically and if they can how it is done.

View 1 Replies View Related

Dynamically Specify Server And Database In Stored Procedure

Feb 28, 2007

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?

EXAMPLE:

SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable

OBJECTIVE:

Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????)


Thanks for any help

View 1 Replies View Related

Building Where Clause Dynamically In Stored Procedure

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

SELECT - Dynamically Determine Fields To Return

Dec 6, 2004

Hello,

I m writing a stored procedure to query a table Population that has the following fields - CityId, CityName, Plus0, Plus10, Plus20, Plus30, Plus40, Plus50, Plus60, Plus70, Plus80. The field Plus0 contains the number of people of age > 0 living in the city, Plus10 contains the number of people of age > 10 living in the city and so on. Given the city id and age groups like 20To40, 50To60, 40Plus, etc., I should be able to query the number of people in the city corresponding to the requested age group. Note that if the requested age group was 20To60, I need to make use of only 2 fields Plus20 and Plus60 in the table to compute this value. And if the requested age group was 40Plus, then I need only the value in the field Plus40. The problem is that a wide variety of age groups can be requested like 0Plus, 10Plus, ... , 80Plus, 0To10, 0To20, 0To30, .... 70To80.

Which is the most effecient way to handle this ?

1. Have a stored procedure that returns all the fields even though only 1 or 2 of them would be actually used ?

In this case, if I returned data for a large number of cities then there would be a lot of unnecessary information that was returned by the query. Lots of data would be passed through the network though most of it would not be used.

2. Have a stored procedure that takes in parameters @Plus0, @Plus10, @Plus20, .. @Plus80 that are bits indicating whether the field was required or not and then using a CASE statement to return values for a field only if the corresponding bit parameter was set, and returning NULL if the corresponding bit paramter was not set ?

In this case, I would be returning NULL for all those fields that were not required for a particular age group. This would save some network bandwidth, wouldn't it ?

3. Pass in the age group itself (ex: 0To20) as a parameter to the stored procedure and have lots of IF statements, one for each age group, that return only the fields that are needed for that age group.

This leads to a lot of code repitition.

4. Use a similar approach as above but use dynamic SQL queries to avoid code repitition.

But using dynamic SQL queries can affect the performance of the stored procedure as they need to be compiled each time.

5. Any other possible approaches ??

Looking forward to your responses,

Thanks much,

bmgun.

View 3 Replies View Related

ODBC TCP/IP Dynamically Determine Port Check Box

Aug 16, 2006

Hopefully a simple answer:

*editted for clarity*

Where does the "Dynamically determine port" check box come from when creating a ODBC connection using TCP/IP in the client configuration? Only shows up in the control panel/adminstrative tools/Data Sources (ODBC) when it is installed. I have 3 computers here that have it (they are for development) but I am finding my users desktops do not have the box and so I am getting errors trying to connect from their desktops.

I am using Excel and Microsoft Access 2000 to connect to the server using the ODBC connection.

I need my users to connect to SQL Server 2005 so I need the "Dynamically determine port" box checked to find the port. Tried typing in the port but that isn't solving the problem or answering my question. So far I have tried MDAC and .NET 2.0 platform installs with no luck.

Thanks for the help,

View 8 Replies View Related

Dynamically Determine Subtotal Visibility In Matrix?

Feb 22, 2007

I'm using SSAS2005 SP2 as a data source, and RS2005 SP2 for reporting.

With the upgrade to sp2, users assigned to an area in a sales location tree no longer get a value returned for AGGREGATE(MyValue.Value) when looking at a region higher than they are assigned.

My MATRIX reports now look like:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_______|
Region Total___________________|_______|
Grand Total____________________|_______|

Before sp2, they had the 5 and 3 values repeated in all total rows.

My thought was to hopefully dynamically hide the sub region and region groupings and their related subtotals - but when trying to do this, only the grand total shows, and its values are blank.

When a person can only see Area1, I'd like the report to look like this:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|

but if they could see everything from the cube, I would like those sections to show, because they have values in them:

_______________________________|___|___|
Region____Sub Region____Area1__|_5___3_|
Sub Region Total_______________|_10__5_|
Region Total___________________|_20_11_|
Grand Total____________________|_42_13_|

View 3 Replies View Related

Want A Stored Procedure To Dynamically Select Views Pulled From A Table.

Feb 1, 2007

I am building a dashboard features that allows user to select reports from a dropdownlist. It is pulling from a table called Reports (cols: ReportID, Description, sqlView) In this Report table the report is associated to a view that queries the report.
And the user's selections are stored in table called UserReport (cols: userID, ReportID, createDt) .
I need to get a Dataset to contain datables of all reports selected. (for example a user select 3 reports, the dataset should contain 3 datables that represent the report).
I want to accomplish this by create a store procedure that queries the Reports table and then dynamically executes the views that related to the user selected reports. Can anyone give me an example on how to create the storeprocedure? 
Thanks,
CG 
 
 
 

View 3 Replies View Related

Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)

Jul 23, 2005

Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa

View 2 Replies View Related

How To Retrieve The Result From A Stored Procedure That Executes A Dynamically Built Up Sql Query?

Apr 2, 2008

We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).

I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?

I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.

Thanks in advance
ripern

-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS

DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '

SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)

SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)

SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)

-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END

View 12 Replies View Related

Columnname In Sql

Oct 2, 2007

 
how to select all column names from sql whose columnvalue is '1' for a specific user
 
 

View 4 Replies View Related

Determine Correct Flow Of Stored Procedures

Mar 12, 2015

We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables.

- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables.

- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).

View 3 Replies View Related

Dynamic ColumnName

Jun 3, 2005

Howdy all,I'm trying to create a sproc that has the column name and search text as input parameters with dynamic SQL...  what I have is:-- Create a variable @SQLStatementDECLARE @SQLStatement varchar(255)SELECT @SQLStatement = 'SELECT * FROM myTable WHERE (''' + @ColumnName + ''' = ''' + @SearchText + ''')'-- Execute the SQL statementEXEC(@SQLStatement)The procedure works (at least in Query Analyzer, once I declare/set @columnname and @searchtext) in that I don't get any errors.  But it returns a blank table with "0 rows affected".  However, if I hardcode the variables, it returns the proper data.  What's wrong with my select statement??  Thanks!!JP

View 2 Replies View Related

&<Tablename&> . . &<ColumnName&>

Apr 30, 2004

Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working

Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b

Can anyone let me know.

TIA,
Ravi

View 2 Replies View Related

How To Select ColumnName From A Table?

Mar 20, 2008

Hi
I know the answere is out there, but i cant find it.I would like to have a dropdownlist in my webpage (asp.net/vb.net) to have values dynamicly collected from my database.The values should be the ColumnName of all columns i have in a speciall table.
 How can i make the dropdownlist to have the ColumnName from every column i the table?My sqlcommand looks like this down under but i would like me.ColumnName to be a ColumnName value.New SqlCommand("Select * from tblmytable where " & Me.ColumnName & " = 'true'", myConnection) Greatful for help!/ Mitmit 

View 5 Replies View Related

WHERE ColumnName = 'this' AND AnotherColumnName IS NOT NULL

Nov 9, 2006

Using SQL Query Analyzer how do I do something like:WHERE columnName = 'this' AND anotherColumnName IS NOT NULLI.E.SELECT *FROM CES_DATAWHERE DIV = 'MW'AND Not IsNull ROORDER BY RO

View 1 Replies View Related

T-SQL (SS2K8) :: Detect / Determine Data Stored In Varbinary Field

Oct 21, 2014

I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.

For example this query returns readable text from this data.

0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column

SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB

However this column has the following query results.

0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column

--this query returns empty result

SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG

--this query returns no change???

SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG
0001000000FFFFFFFF01000000000000000C....etc

Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?

Something like:

1. Try to cast as varchar to see if it is text.
2. Turn into a byte array and see if it is a jpg
3. Turn into a byte array and see if it is a pdf
4. Convert it to hex and then cast as varchar
5. etc....

View 3 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

How To Do Store Procedure Dynamically

Jul 8, 2014

below Store procedure is working fine. But fixed the Year.

How can do the year dynamically select by user?

SELECT * FROM ( SELECT salesEXE,SalesEXEName,name,gross,YearNo FROM vw_Pivot_Month_ALL) TableYearNo
pivot (Sum(gross) for [year] in ([2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017],[2018],[2019],[2020])
)pivotTable

View 1 Replies View Related

SELECT Columns By Column-index NOT By Columnname!

Nov 20, 2005

Hello all,

how can I select one or more columns from a table by column-index and NOT by columnname?

e.g.:

SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1

and NOT like this:

SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1

Is that possible in MS-SQL 2000?

Thanks a lot in advance

kind regards

Otto

View 11 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Export Procedure Results To Excel Dynamically

May 16, 2007

Hi,

I am trying to create a DTS package that uses a sql stored procedure to generate a set of results and export those results to an excel spreadsheet on a server.

The trick is that the stored procedure accepts a parameter for Bank_Number (there are 10 of them). Therefore i was wondering if there was a way to somehow create the package to run the stored proc 10 times, each with a different bank number as the parameter and generate 10 different excel spreadsheets, one for each bank with it's results.

Can this be done using DTS or do i have to try another method?

thanks
scott

View 1 Replies View Related

SSIS - Failure Inserting Into The Read-only Column ColumnName

May 10, 2007

Hi folks, I have a little challenge for you, I hope :)

The short story is as follows: I have multiple rather well normalized tables, and a view which connect them all togheter. In one of the tables I have an xml column, which I in the view to varchar(4000), since SSIS doesn't like xml all that much. To make the view updateable I have an instead of trigger placed on it, which works quite perfectly from T-SQL. SSIS however, claims that I cannot insert to a read-only column. Does anyone have the slightest idea how I can get around this?

The only solution I can think of is having two columns for the XML data, one as varchar and the other as varchar, then having a trigger updating the xml column when the varchar is updated, but that would require recursive triggers, which I am not too happy with.

Cheers

View 11 Replies View Related

How To Dynamically Assign Database Name In Query Or Store Procedure?

Sep 22, 2006

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @MyDatabaseName varchar(30)

set @MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

View 8 Replies View Related

How To Save Filename As Columnname In Flat File Dest Config

Oct 20, 2006

I have created a SSIS pkg (datasource and flat file destination)

I need to have the flatfile renamed after each exec to a column name that exists in the table I am running the pkg against.

Where does the code get put to make this change after the file has been created?

Basically if I could run T-SQL after the file creation, I could make it work.



thx..

View 1 Replies View Related

Dynamically Created Stored Procs???

Mar 8, 2004

Okay. Scenario:

We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

Or if not, is there some round-about yet effective way of doing this?


Thank you in advance for any help.
-ZanderB

View 8 Replies View Related

How To Exec Stored Proc Dynamically

Jul 23, 2005

HelloI have 2 procedures setup in master database, sp_RebuildIndexesMain andsp_RebuildIndexesSubThe Sub just shows and execute DBCC commands for passed databasecontextsp_RebuildIndexesSub(@listOnly bit=0, @maxfrag Decimal=30.0)This runs fine if I do pubs..sp_RebuildIndexesSubHowever when run thru. the Main proc, I get Incorrect syntax near'pubs'.The main proc isCreate Proc sp_RebuildIndexesMain(@dbName sysname, @listOnly bit=0,@maxFrag Decimal=30.0)AsBeginSet NOCOUNT ONDeclare crDbs CURSOR ForSelect CATALOG_NAME From INFORMATION_SCHEMA.SCHEMATAWhere CATALOG_NAME NOT IN ('tempdb', 'master', 'msdb', 'model','distribution', 'Northwind', 'pubs')And CATALOG_NAME Like @dbNameDeclare @execstr nvarchar(2000)Open crDbsFetch crDbs INTO @dbNameIf (@@FETCH_STATUS<>0) --Then no matching databasesBeginClose crDbsDeallocate CrDbsPrint 'No databases were found that match ''' + @dbName + ''''Return -1EndWhile(@@FETCH_STATUS=0)BeginPrint Char(13) + 'Rebuilding indexes on ' + @dbNamePrint Char(13)Set @execstr = @dbName + '..sp_RebuildIndexesSub 'EXEC sp_executesql @execstr, N'@listOnly bit, @maxFrag Decimal',@listOnly, @maxFragFetch crDbs INTO @dbNameEndClose crDbsDeallocate CrDbsReturn 0EndthanksSunitJoin Bytes!

View 5 Replies View Related

Dynamically Named Stored Procedures

Oct 18, 2007

I have a set of stored procedures that return a fixed list of columns from a fixed list of parameters.

Is it possible to use 1 Report file to display these reports, and just switch the stored procedure name at runtime within .NET code, or will this reset the parameter list and/or column list?

View 4 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved