Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End
Execute df_bppr @de = 'NoTitle'
******************
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
--------------------
I created the procedure without any error. But when i execute the procedure it shows the above error
I want to create a proc which dynamically change the default value for more than one column with same default value.
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty. Expressions is empty.
When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Greetings everyone, I am trying to use a c# string with an SQL statement in a data adapter (.NET 03) The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: WHERE (login = '" & test & "') WHERE (login = '" + test + "') any ideas? PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query. Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window THANKS!
I've created an sql statement: select * from fin_installment where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial
which is in an Dataset's TableAdapter. This variable receives its value during the form init and it is an integer. When I start the page the folowing error message is displayed:
" An error has occurred during report processing. Exception has been thrown by the target of an invocation. Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. "
So my question is that how can I use variables in sql statement in dataset?
Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code:
Code:
-- this example returns results SELECT whatever FROM mytable WHERE whatever LIKE 'blah%';
Code:
-- this example returns no results DECLARE @test VARCHAR; SET @test='blah%';
SELECT whatever FROM mytable WHERE whatever LIKE @test;
Any ideas why the version using the variable would not work?
I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?
Here is the modified code:
/* ** Drop_Users_Company.sql ** ** This script will remove all users from the DYNGRP in the company database ** specified. It will then drop the DYNGRP and readd the DYNGRP to the company. ** It will then add all users back to the DYNGRP based on the SY60100 table. ** NOTE: You will need to replace %Companydb% with the company database ** name. */ /* Instead of replacing %Companydb% (in each USE statement) with the name of the single company database that this script is supposed to work on, I've added @cCompany to hold the company DB name through each iteration of the outside cursor/while loop. */
declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */ declare @cStatement varchar(255)/* Misc exec string */ declare @DynDB varchar(15)/* DB Name exec string */ declare @DYNGRPgid int/* Id of DYNGRP group */
/* ** Loop through all company databases, emptying the DYNGRP group. */ SET QUOTED_IDENTIFIER OFF
use DYNAMICS
/* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */ declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')
OPEN C_cursor FETCH NEXT FROM C_cursor INTO @cCompany WHILE (@@FETCH_STATUS <> -1) begin use @cCompany select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')
declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers where gid = @DYNGRPgid and name <> 'DYNGRP'
set nocount on
OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor /* ** Do not delete the group to attempt to preserve the permissions already ** granted to it. */ use @cCompany if exists (select gid from sysusers where name = 'DYNGRP') begin exec sp_dropgroup DYNGRP end /* ** Recreate the DYNGRP group in all company databases. */ use @cCompany if not exists (select name from sysusers where name = 'DYNGRP') begin exec ("sp_addgroup DYNGRP") end
end DEALLOCATE C_cursor
______________________________________ Thanks for any help you have.
Declare @MyCode nvarchar(20); Set @MyCode='ABC' set @int_rowcount=(SELECT count(hoten) FROM @MyCode) I run it but still errors ! How can i implement above statement ? Thank you very much !
Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???
I am testing a very simple query that use variable for sort direction and sort expression
DECLARE
@SortExp nvarchar(256),
@SortDir nvarchar(10)
Set @SortExp = 'curTime'
Set @SortDir = 'DESC'
Select * from table where recID < 20 order by @SortExp @SortDir
and i got this error...
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery()
When I don't use a variable after SET statement, everything work fine. This code works fine: Dim variable As Integer Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery()
could anyone please help me to resolve this issue? here's my sql query which retrieve last 3 month data t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) ) i need to pass a variable to retrieve data based on user requirements. i tried this way, dim mno as n mno=4 t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") its not working. can i achieve this using stored procedure? or can i directly pass a variable to sql synatax? thanks for any help
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName
I am having difficulties with some sql syntax with sql server 2000. I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String. Ex. Declare @varA varchar(12) select @varA = (select top 1 Value from #temp)
Update TableX set @varA = y.ColTest from TableX x, TableY y where x.Colid = y.Colid
It sets the variable = to the last value from TableX.ColTest I want the Update statement to update the value for the Variable which represents the correct column to update.
Hi, I want to use a variable in a 'use' statement... but, I cannot figure out the syntax, nor do I know if it is possible... Here is an example SQL script: /*-----------------------------------------------------*/
DECLARE @DataBase varchar(60) --Declare cursor for all DBs except master, MSDB, Model, tempdb DECLARE curdb CURSOR for select name from master..sysdatabases where name not in ('master', 'MSDB', 'Model','tempdb')for read only
--Open and perform initial fetch open curdb fetch curdb into @DataBase
--While there are databases to process, process each DB While @@fetch_status = 0
PRINT @DataBase use + ' ' + @database --or, use @database
fetch curdb into @DataBase
end
/*------------------------------*/ Thanks, Michael
I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.
Code:
DECLARE @myval varchar(50),@username varchar(50) DECLARE @rolelist varchar(2000) SET @rolelist = '' SET @myval = 'user a,user b' select @myval = ''''+ replace(@myval,',',''',''') + '''' print @myval
DECLARE User_Cursor CURSOR FOR select distinct eusername from euser where eusername in (@myval)
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @username
SET @myval = @username SET @rolelist = @username WHILE @@FETCH_STATUS = 0 BEGIN SET @rolelist =+ @rolelist + ',' + @username
FETCH NEXT FROM User_Cursor INTO @username END CLOSE User_Cursor DEALLOCATE User_Cursor print @myval print 'rolelist' + @rolelist GO
I am at a loss any suggestions would be greatly appreciated.
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @Where. (I tried with DEBUGGING option of Query Analyzer also).
============================================= SET @Where = '' if IsNull(@Input1,NULL) <> NULL Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL FOR SELECT firstname FROM school WHERE school ='ABC' + @where ============================================= Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:
Incorrect syntax near '='.
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
Here is my code block:
Code Snippet
declare @permissionID int;
if exists(select @permissionID = Id from Permission
I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used. I've read BOL and bought a book. Can't say either one has really helped.
I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.
What I'm trying to do now is parameterize a SQL statement.
I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.
Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.
I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?
I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005. I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value. However this value is not then working in the following line: strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]" This statement is not reading any value for ProjectID and so is selecting all from the table..... How do I write this statement to pick up the variable above????? Thanks in advance for your help! Clare
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim ds As New Data.DataSet Dim da As Data.SqlClient.SqlDataAdapter Dim strSQL As String Dim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]" Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(strSQL, connString) da.Fill(ds) ds.Tables(0).Columns.Add("imgFile")
For Each tempRow As Data.DataRow In ds.Tables(0).Rows tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID")) Next ImgGrid3.DataSource = ds ImgGrid3.DataBind()
I have a page that is displaying different movie covers. They each have their respective genres stored in several keys (genre0, genre1, genre2). Now I can search within the movies by changing the url variable movies.aspx?genre=action but if I simply go to movies.aspx, I'm wanting all genres to appear. But for me, the page is blank if I don't specify the genre. So I've had to start filling in the genre All for all movies so I can go to movies.aspx?genre=all but I know this can't be necessary. Is it my SQL statement for the datasource causing the problem? Code is in VB. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Movies] WHERE [genre0] = @strGenre OR [genre1] = @strGenre OR [genre2] = @strGenre"> <SelectParameters> <asp:QueryStringParameter Name="strGenre" QueryStringField="genre" /> </SelectParameters> </asp:SqlDataSource>
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20)) AS BEGIN TRANSACTION SET NOCOUNT ON
DECLARE @ProjectNumber int SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','') SELECT MAX(@ProjectNumber) FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.',''); END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
Hi Hope somebody can help me with a problem that i am having. I have the following piece of code <% Dim sqlConn As SqlConnection Dim sqlCmd As SqlCommand Dim sqlReader As SqlDataReader
sqlConn = New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password") sqlCmd = New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") + ")", sqlConn) sqlConn.Open() sqlReader = sqlCmd.ExecuteReader() %> <%
%> The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in. Can anybody help please? Thanks Marcus
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"? When I try that is says "Must declare the table variable @Table_Name". Thanks!
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =) I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName> SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: XXXXXXXXXXXXX -- Create date: 10/4/2007 -- Description: This proc is designed to pull all CSA -- part sales from XXXXXX business system and upload them -- into the local XXXXXXXX Database for commission reporting -- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int As INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount ) SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')
In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September. This is the error message I get when I try to create the proc: Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17 Incorrect syntax near '+'. ~~~ Thanks All~~~
Hello everyone, I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases). I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement? For example: SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions. Thanks Scott
