I'm new to SQL Server, but an experienced .Net developer. I'm trying
to accomplish a query the most efficient way possible. My question is
if you can define a temporary variable within a query to store tables
or fields. (Like the LET clause of LINQ) My query makes use of
subqueries which filter my table (WHEREs, not SELECTs) in the same
exact way. I'd like to have a subquery at the beginning of my query
to filter the table(s) once, and then SELECT off it of later in the
query.
Here is an (utterly poor) example. No, this is not from my project.
My filter is a little more complex than 'c=@p'.
('c' is a column/field, 't' is a table', '@p' is a parameter)
SELECT *
FROM (SELECT COUNT(c) FROM t WHERE c=@p GROUP BY c)
CROSS JOIN (SELECT c FROM t WHERE c=@p)
Bottom line, would something like the following be possible?
@v = (SELECT c FROM t WHERE a=@p)
SELECT *
FROM (SELECT COUNT(c) FROM @v GROUP BY c)
CROSS JOIN (SELECT c FROM @v)
I'd like to know if this is possible within a query, but I can move to
a Stored Procedure if I must. (I'll still need help then.)
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
Hi I'm a n00b at ASP.Net and C# but the company I work for requires me to know this and develop their website using these languages. Anyway I've been doing pretty good so far I got the page looking as it should etc and databases all set up, now I've hit my first real problem... I am using Visual Studio 2005 and in the Web Developer section. I want to make a SELECT query using the <asp:LoginName> control as the where clause. e.g. if admin logs in there is a welcome message : Welcome Admin now I want to use the 'Admin' <asp:LoginName> as the SELECT in my query i.e. SELECT * FROM tbla WHERE ([fldCustomerCode] = 'LoginName' ) I was just really wondering if that is in the .aspx file or the aspx.cs code behind file? Thanks for any help given
I’ve reconfigured Microsoft’s IBS Store shopping cart to function within a small e-commerce website. What I am trying to do is to modify the code slightly in order to use a third party credit card processing center. The situation is this: once the customer clicks the final "check out" button, a stored procedure writes all of the product ordering information into the database. I, then, capture what they're wanting to purchase with the following SQL statement:
I'm trying to create a stored procedure with a dynamic update statement depending on the variables received. I receive a sql error "Incorrect syntax near the keyword 'WHERE'." although the variable, @tempvar, appears correct. Any ideas? ---- DECLARE @tempvar varchar(1025), @SolutionDetail varchar(1000), @hours varchar(12), @id int
if @SolutionDetail <> " " set @tempvar = "SolutionDetail = " + "'" +@SolutionDetail + "'"
IF @hours <> " " set @tempVar = @tempvar + ", hours = " + "'" + @hours +"'"
UPDATE WorkOrderTbl SET @tempvar WHERE workorderid = @id
Hi all, I was wondering if anyone can help me figure out how to insert a variable into a query using Visual Studio 2005 with the SqlDataSource control. I cant seem to be able to enter a var into the query parameters.
this is my SqlDataSource: <asp: SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLookinLikeConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" SelectCommand="SELECT tblDiaryEntries.DiaryEntryID, tblDiaryEntries.EntryDate, tblDiaryEntries.Subject, tblDiaryEntries.DiaryEntry, aspnet_Users.UserName FROM tblDiaryEntries INNER JOIN aspnet_Users ON tblDiaryEntries.UserID = aspnet_Users.UserId WHERE UserName=@UserName ORDER BY tblDiaryEntries.EntryDate DESC" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [EntryDate] = @EntryDate, [Subject] = @Subject WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO tblDiaryEntries(UserID, EntryDate, Subject, DiaryEntry) VALUES (@UserId, GETDATE(), @Subject, @DiaryEntry)"> <DeleteParameters> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp: Parameter Name="DiaryEntry" Type="String" /> <asp: Parameter Name="EntryDate" Type="String" /> <asp: Parameter Name="Subject" Type="String" /> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> <asp: Parameter Name="UserId" /> </InsertParameters> <SelectParameters> <asp: ProfileParameter DefaultValue="Anonymous" Name="UserName" PropertyName="UserName" /> </SelectParameters> </asp: SqlDataSource> What I want to do is to tell the SqlDataSource that @UserName = this.User.Identity.Name, but I only know how to do that in code-behind, not sure how to insert it into the code above.Any ideas?
If I have a query string that is to be stored in a database, for example
Code:
SELECT prod_id, prod_name, prod_desc FROM products WHERE prod_id = 'variable'
how can I put a variable identifier into this string so that when I need to run the query I call it from the database and simply insert the relevant variable in the correct place.
Is there an appropriate way of doing this in MS SQL Server?
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
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 am brand spankin new to stored procedures and don't even know if what I want to do is possible. From everything I've read it seems like it will be. I have a table, punchcards. In this table are all the punch in/out times for a week. I want to create a stored proc to calculate how many hours a punchcard entry is.
Thats the dream.
The reality is that I can't even get a tinyint from a table to load to a variable and be printed out. I am using sql server 8.
Here is what I have as of this moment for my sp.
ALTER PROCEDURE usp_CalculatePunchcard AS DECLARE @dtPP DateTime SET @dtPP = (SELECT thursday_in1 FROM punchcards WHERE (punchcard_id = 1)) /* Also tried.... SELECT @dtPP=thursday_in1 FROM punchcards WHERE (punchcard_id = 1) */
PRINT @dtPP
RETURN /* for some reason i can't use GO ... even though every document i've read on stored procedures has used GO and none use RETURN */
The only output this is producing is ' Running dbo."usp_CalculatePunchcard". '
Any help would be greatly appreciated as I am about to kick someone/something.
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint DECLARE @Instance varchar(100) set @Instance = 'The Instance' IF (EXISTS (SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a)) SET @Found = 1 ELSE SET @Found = 0
when I run this query against an Oracle 9 datasource I get a message saying 'not all variables bound'
select <some fields> from star where nr_jahr_star = :Year
my datasource is correct as the same report runs fine if I just put in a fixed year and I have the Oracle client software on both my PC and the RS server
I've tried various combinations with the parameter settings also with no luck.
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename. I get the error that it can't open a database @databasename. Can I declare the database object in T-SQL?
/* Get Name of Server & declare variables */ declare @sname varchar(30) declare @db1 varchar(30) declare @db2 varchar(30) declare @db3 varchar(30) declare @dbf1 varchar(30) declare @dbf2 varchar(30) declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1' Set @db2 = @sname + 'database2' Set @db3 = @sname + 'database3' Set @dbf1 = @db1 + 'RL_log' Set @dbf2 = @db2 + 'RL_log' Set @dbf3 = @db3 + 'RL_log'
print @db1 print @dbf1 go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED) go ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED) go ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED) go .....
When I run it, I get the following errors: ..... The name of this server is: KANSASCITY KANSASCITYdatabase1 KANSASCITYdatabase1RL_log Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near '@db1'. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@db2'. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@db3'. .....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH: I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
I want to change database in my SQL-script like this: DECLARE @DB_NAME varchar(30) SELECT * FROM @DB_NAME.dbo.TableName, but it is syntactically incorrect. I could use USE (@DB_NAME) but I write Stored Procedure, it is not allowed using USE statement in any Stored Procedures.
I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms. I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?
In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL. Would you please tell me how can i use a variable in MS SQL query?
I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.
To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?
Is there some sort of tutorial out there that explains use of global variables?
Basically I am creating a wizard(using forms) within a database to import client files and reformat them into the format that we need.
I have a form that list all 26 column headings that I need and tied to each column headings is a combo box that that lists the column headings from the imported client file.
I need to create a query that either appends it to an already formatted file or creates a new table with the column headings.
The problem is the field headings from the imported file change. This is the sql that I used that doesn't work:
INSERT INTO Formatted_Data ( Field X ) SELECT Imported_Data.Forms![Import Data-3]![Combo33] FROM Imported_Data;
Because Imported_Data.Forms![Import Data-3]![Combo33] is not recognized. I think if I could create a variable to replace the Forms! part then it would work but I do not know how to do this,
I have a simple task flow which takes data from an external table and puts it into a SQL server table. In the SQL server table I have a column which flags the data source. I have set up a variable in the variable window to hold a constant which was the appropriate value for the column. I cannot see how to map the variable I have created to the column in my output table.
Not sure if variables do what I think they do, can anyone give me a simple overview, and point me in the right direction to add my constant into the output table.
