SQL Statement In Variable

Sep 3, 2007

Hello Everyone,
I wanted to pass a SQL statement thru a variable, and use that variable in my source component.

SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT
FROM DIM_CLINIC
where load_dt > ?

I had created a variable with my SQL statement and mapped that variable in my source component.
Its giving me some error.

Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command.


Please do inform me about the solution for having a parameter in my source SQL Statement.

View 3 Replies


ADVERTISEMENT

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

Using C# Variable With SQL Statement

Nov 12, 2007

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!

View 8 Replies View Related

Variable In An Sql Statement

Dec 14, 2005

Hi,

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?

View 3 Replies View Related

Using Variable In LIKE Statement

Dec 13, 2007

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?

Patrick

View 3 Replies View Related

USE Statement With A Variable?

Jun 17, 2004

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.

View 1 Replies View Related

My Variable In Sql Statement

Aug 11, 2006

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 !

View 6 Replies View Related

USE Statement With Variable

Jul 20, 2005

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???

View 2 Replies View Related

SQL Statement With Variable.

Apr 4, 2008

Hi ,

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.


Is there anyway to do this task.

Thanks

Ddee

View 3 Replies View Related

Can Variable Be Used In SQL UPDATE Statement In VB.NET

Dec 16, 2007

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()
 
Please, if someone can help me in this...thanks..

View 2 Replies View Related

Passing Variable To Sql Statement

Apr 28, 2008

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   

View 8 Replies View Related

Use A Table Name As A Variable In The From Statement

Oct 26, 2005

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

View 4 Replies View Related

Using A Variable In An Update Statement

Jan 2, 2001

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.

Any ideas?

Thanks,

Daniel

View 1 Replies View Related

Want To Use A Variable In A &#39;use DB&#39; Statement In SQL Script

Jan 18, 2002

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

View 1 Replies View Related

USE @dbname (Use Statement With Variable)

Jun 12, 2002

Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.

I want to execute the SP withing each db.

Thanks

View 2 Replies View Related

How To Use Transact SQL Variable In A SQL In Statement

Mar 2, 2007

Hi all,

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.

View 4 Replies View Related

Variable In CURSOR Sql Statement (was Please Help Me)

Dec 24, 2004

Hi All,

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 ?

Thanks !

View 3 Replies View Related

Variable In A Select Statement

Sep 6, 2006

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.

select @column_name from table

View 2 Replies View Related

Set Variable Value In CASE Statement?

Jun 29, 2012

Is it possible to set a value to a variable if a case statement is true?

like this:

CASE
WHEN Utable.type = 2 THEN U.Username2 + (@Uname= 2)
WHEN Utable.type = 3 THEN U.Username3 + (@Uname= 3)

Ive tried above and:

"+ (select @Uname = 2)"
"+ (SET @uname = 2)"
"; SET @uname = 2"
...

and several other things but non works.

variable's = INT
"Utable.type" = INT
"U.Username"2/3 = nvarchar(50)

View 2 Replies View Related

Variable In DDL Statement In Procedure

Apr 15, 2008

Create table tbl(title nvarchar(40))

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.

Vijai

View 6 Replies View Related

How To Set A Variable In An If Exists Statement

Feb 28, 2008



Hello,

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

where [Description] = 'SettlementReport')






Thanks,
Flea#

View 3 Replies View Related

Passing A Variable To A SQL Statement

Mar 10, 2008

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.

Is my approach fundamentally flawed in some way?





View 25 Replies View Related

Using A Variable In A GROUP BY Statement

Sep 2, 2007



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?

Thanks a lot for your replies

Greets from Switzerland

Chris

View 3 Replies View Related

Incorrect Sql Statement - Not Reading Variable

Dec 5, 2006

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()
 
End Sub

View 12 Replies View Related

Correct SQL Statement For URL Variable Display?

Apr 6, 2007

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 &#13;&#10;OR [genre1] = @strGenre &#13;&#10;OR [genre2] = @strGenre">
<SelectParameters>
<asp:QueryStringParameter Name="strGenre" QueryStringField="genre" />
</SelectParameters>
</asp:SqlDataSource>

<asp:DataList ID="DataList1" runat="server" DataKeyField="movieID" DataSourceID="SqlDataSource1" RepeatColumns="5" RepeatDirection="Horizontal" CellPadding="5" BorderStyle="None" BorderWidth="0px">
<ItemTemplate>
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%# "movie.aspx?id=" & Eval("movieID") %>'>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("imageURL", "{0}") %>' BorderStyle="None" /><br />
</asp:HyperLink>
</ItemTemplate>
</asp:DataList>  

View 2 Replies View Related

Variable For The Table Name In A SELECT Statement.

Apr 23, 2007

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. 

View 3 Replies View Related

Filtering SQL Statement By Server Variable

Apr 27, 2007

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()
%>
<%
 
Dim strPCNAme As String
 
sqlReader.Read()
 
strPCNAme = sqlReader("UserLoggedIn")
Response.Write(strPCNAme)
 
%>
 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

View 2 Replies View Related

Using A Variable For Tablename In Select Statement?

Sep 19, 2007

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!

View 1 Replies View Related

Calling Variable Inside T-SQL Statement

Oct 4, 2007

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~~~
 

View 9 Replies View Related

Using A Variable To Specify Table Column In Sql Statement??

Feb 6, 2008

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

View 15 Replies View Related

SQL Select Statement With A 'string' Variable?

Apr 16, 2008

I'm trying to add a 'change password' control to my site and seem to be having some issues.  I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly.    Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user.  However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell)  public void InitPage()    {            IPrincipal p = HttpContext.Current.User;            String myid = HttpContext.Current.User.ToString();            SqlServer sqlServer = new SqlServer(Util.SqlConnectionString());            DataTable dt;            SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString);            SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn);            DataTable UIDtable = new DataTable();            cmd1.Fill(UIDtable);            User_Id.Value = UIDtable.Rows[0]["ID"].ToString();            dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value); 

View 1 Replies View Related

Variable Not Holding Value For Select Statement

Dec 7, 2005

this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID  ))) but for some reason it will not work with the next select statement...
 
can someone make the pain go away and help me here..??
 
erik..
 
GOSET ANSI_NULLS ON GO
ALTER  PROCEDURE AA
ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'
 DECLARE @SQL NVARCHAR(4000)  DECLARE @UserID INT  DECLARE @UsersMaxID INT  DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
 WHERE ' + @GenericColumn +' = ''' + @GenericValue  + ''''
EXEC sp_executesql @SQL
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29
SELECT * FROM #UsersTempTable
 
 ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE

View 1 Replies View Related

How Do You Place A Variable In Your Gridview Sql Statement?

Apr 12, 2006

I have the below select command in my gridview that uses a library name.table approach in the sql. I created a label on the page and want to pass the label in where i have the libarary1 text in the select statement. This will allow me to swap out the production library with my development library when im working on the site (thru the use of a site variable) 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:as400con %>"ProviderName="<%$ ConnectionStrings:as400con.ProviderName %>" SelectCommand="SELECT * from  library1.table WHERE COMPANY = ? and ack = ? and HORZN_DATE <= ?">
<selectparameters><asp:controlparameter name="company" controlid="accts" propertyname="SelectedValue"/><asp:controlparameter name="ack" controlid="podropdown" propertyname="SelectedValue"/><asp:controlparameter name="dt" controlid="htoday" propertyname="Text"/></selectparameters>
How would I insert a label.text value into the select statement replacing the library1 ? I tried to use the asp:controlparameter adding the text and placing a ? mark in place of the library1 but it failed.
Thanks for any help you can lend on this,
Todd
 
 

View 5 Replies View Related







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