SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to apply a varibale to an insert statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dave1816
Starting Member

United Kingdom
8 Posts

Posted - 12/21/2012 :  05:05:45  Show Profile  Reply with Quote
Hi, I have some code which copies one user details over to another database which works well. However, now I want to replicate a user within the same DB, and just changing the username, password and e-mail addy.

Therefore is it possible to amend the select statement below and pass the variables into the table? if not what the best way to approach this? as I’m receiving error when I try.



[code]if object_id('tmp_copyuzr') is not null/* FILL IN THE USER INFORMATION WITHIN THE SINGLE QUOTES */
SET @Username = '' --<--- TYPE IN the Username (what they use to log in to SL)
SET @FROMdb = '' --<--- TYPE IN the name of the app db the User is in
SET @TOdb = '' --<--- TYPE IN the name of the app db you want to move the User to
-- Example: @FROMdb= 'OHIO_App' @TOdb = '[SRV001\INST4].TEXAS_app'
-- Note: Only use the full linked server name if the TOdb is on a different SQL server

/* Copy Username record */
SET @SQL='
INSERT INTO ' + @TOdb + '.dbo.usernames (
Username
,EditLevel
,SuperUserFlag
,UserPassword
,SQLServerLogin
,SQLServerPassword
,UserDesc
,EmailAddress
,WorkstationLogin
,ConcurrentSessionsSpec
)

SELECT
Username
,EditLevel
,SuperUserFlag
,UserPassword
,SQLServerLogin
,SQLServerPassword
,UserDesc
,EmailAddress
,WorkstationLogin
,ConcurrentSessionsSpec
FROM ' + @FROMdb + '.dbo.usernames t2
WHERE t2.username=''' + @username + ''''
execute @Severity=sp_executesql @SQL
if @Severity = 0 print '*** Username record inserted successfully'
else begin print '### Error inserting username record.
Rolling back transaction.' rollback tran return end [code]

D.ARNOLD

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  05:11:45  Show Profile  Reply with Quote
you need to declare the variables before using inside sp_executesql

see

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dave1816
Starting Member

United Kingdom
8 Posts

Posted - 12/21/2012 :  05:52:17  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you need to declare the variables before using inside sp_executesql

see

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx




Hi visakh16,

I have already done this at the top of my code (I havn't added it to this forum) My question really is if im using a select statement how do input a varibale for say the username? I get error 'Invalid Column Name if I put it into the statement.





D.ARNOLD
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 12/21/2012 :  06:16:02  Show Profile  Reply with Quote
In that link see 'Substituting Parameter Values' section to understand this concept..

See this example..

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

Here @SalesID is used in WHERE clause...
While executing your prepared SQLString, you have to pass parameters definition as well as input values




--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/21/2012 :  06:37:49  Show Profile  Reply with Quote
quote:
Originally posted by dave1816

quote:
Originally posted by visakh16

you need to declare the variables before using inside sp_executesql

see

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx




Hi visakh16,

I have already done this at the top of my code (I havn't added it to this forum) My question really is if im using a select statement how do input a varibale for say the username? I get error 'Invalid Column Name if I put it into the statement.





D.ARNOLD


Nope you've not

I'm suggesting to include declaration of variables in sp_executesql call. you need to pass it as an argument as link shows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000