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?
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
After the staging_temp data gets inserted into main table.my probelm is to handle such a file where number of columns are more than the actual table.
If you see the sample rows there are 4 column separated by "¯".but actual I am having only 3 columns in my main table.so how can I get only first 3 column from the satging_temp table.
I have 3 web identical web apps whose only diff is that they access different SQl Server DB's. I use the SQLDataSource in a number of pages to retrieve data from the db. The apps all use Forms Auth. I would like to be able to see who is the logged on user user and assign the approp connection string to all the SQLDataSources in the app. For example when user UserA logs in they are retrieving data from on db but when UserB logs in they are retrieving data from another db. I am sure this can be done but could use a little guidance. Thanks in advance.
How can i assign select statement to a sqldatasource I mean if the user clicks button 1 sqldatasource=-"select * from customers where status='ClOSED' " or else if the user clicks button2 sqldatasource="Select * from Customers Where Status='OPEN' " I want to bind the sqldatasource to a gridview finally . Is this possible ? I tried sqldatasource filters But am reallly confused Any suggestions/solutions are appreciated thankyou
Hello All, How to assign the DataSource to Server Report which is stored in the remote machine. Now i need to work on it. If you have any solution please pass on it to me.
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')????)
Hello AllI get stuck in one problem , Please help me and excuse me on the poorKnowledgein SQL Server Store procedureQ1...I want to return a string type value from store procedure , Is itpossible to do it.if posible then Please guide us how we can do it .Q2...I search a lot on net but i am unable to understand the ExtendedStore Procedure.How to register it and how to use in our store procedure .Q3 ... How to use xp_sendmail.dll in SQL Server.Thanking youWith regardsTarun
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
hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..
select * from authors
OR
create procedure authors as
select * from authors
lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ? thanks for your input
I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion. Thanks Maroof Khan
CREATE TABLE #KeyWord ( [QueryId] int IDENTITY (1, 1) NOT NULL , [QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND) [SKey] nvarchar (200) NULL ) ON [PRIMARY]
--target table:
CREATE TABLE Test ( [PId] int IDENTITY (1, 1) NOT NULL , [PTitle] nvarchar (200) NULL ) ON [PRIMARY]
================ I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
I want to write more than one insert query in single store procedure. or Is it possible to insert a row into multiple tables with a single insert into query. Thank You.
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
I have a urgent problem, and can not google any useful answer. Please help me!
In my CLR store procedure, I have used a context connect, and I want to creat another connect to the same database. I have set the database permission to external, creat asymmetric key in my assembly, and creat login for that key. The dbo is the owner.
When I use this connect string as store procedure input, and I opened this new connect in the CLR sp, it throw an exception --"***.MDF is in used, failed to connect"!!
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
hello all.., i want to make procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy" i have 2 database like: bank.dbo and games.dbo in games.dbo, have a table name is order(user_id,no_order,date,totalcost) in bank.dbo, have a table name like is bill(no_bill,balance) this is a list of bill table no_bill balance 111222 200$ 222444 10$ this is a list of order table user_id no_order date totalcost a 1 1/1/07 50$ when customer insert no_bill(111222) in page and click a button, then bill table became no_bill balance 111222 150$ 222444 10$ when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough" is procedure can take data from 2 database?mystore procedure like:ALTER PROCEDURE [dbo].[pay]( @no_bill AS INT, @no_order AS int, @totalcost AS money)ASBEGIN BEGIN TRANSACTION DECLARE @balanc AS money SET @balanc= (SELECT [balance] FROM [boysqlexpress.Bank.dbo.bill] WHERE [no_bill] = @no_bill) UPDATE [bill] SET [balance] = @balanc - @totalcost WHERE [no_bill] = @no_bill COMMIT TRANSACTIONEND it's output message "Invalid object name '<boysqlexpress>.Bank.dbo.bill'.Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[pay]." plss.. help...
I would ask if there some precaution on creating stored procedure on a database member of an always on group. I copied the stored procedure on all nodes but I'm not sure if this is enough to make it works in case of failover ?
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line (@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen: @Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
Hi My webpage contain three dropdown box and one button. One dropdown to choose hospital_id, second dropdown to choose project_id and third dropdown to choose version_no .After choosing these three and when the button is clicked i want to run this sql query INSERT INTO version(project_id,hospital_id,date_created,comments) SELECT project_id,hospital_id,date_created,comments FROM version where version_no=@version_no and project_id=@project_id and hospital_id=@hospital_id. Just i need the code in asp.net 2.0 , VB , when the button is clicked the above query should run and the parameters (values) should take from dropdown box. Could anyone please send the solution for the above problem.
